1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

sorting in excel

Discussion in 'Business Applications' started by ckaas, Sep 26, 2003.

Thread Status:
Not open for further replies.
Advertisement
  1. ckaas

    ckaas Thread Starter

    Joined:
    Feb 3, 2003
    Messages:
    133
    in excel 2002, if i have a list of names and titles, such as:

    John Doe Mailings
    Sarah Jones Bazaar
    Terrence McLeod Mailings
    ...

    is there a way to have excel sort according to what title the people are associated with? thanks,

    Christian K.
     
  2. cybertech

    cybertech Retired Moderator

    Joined:
    Apr 16, 2002
    Messages:
    72,115
    So you want to sort by the last notation in the line, like Mailings and Bazaar?

    Here's what I'd do. Using the next column over put the words Mailings or Bazaar, remove the existing Mailings and Bazaar then sort by the new column.

    I'm sure there is an Excel guru who could make a fancy macro to get you there too.

    :D
     
  3. Bruce319

    Bruce319

    Joined:
    May 14, 2003
    Messages:
    379
    All you have to do is highlight the columns, may it be two or three depending if you used one column for First name and another column for their Last name and the third as their title. Once highlighted, click on Data then Sort. In the Sort by drop down menu select the column letter that corresponds to the location of the Title information.
     
  4. cybertech

    cybertech Retired Moderator

    Joined:
    Apr 16, 2002
    Messages:
    72,115
    It looked to me like there was only one column containing the entire string.

    I could be wrong, I was once, but it's been so long ago I can't remember what I was wrong about

    :D :) :D
     
  5. tCoop61

    tCoop61

    Joined:
    Aug 5, 2003
    Messages:
    47
    It ain't pretty, but here's a formula to retrieve the title, assuming its always the 3rd word (followed by the second space):

    =RIGHT(B4,LEN(B4)-(FIND(" ",B4,(FIND(" ",B4)+1))))

    where B4 is the name/title data.

    Hope this helps.
     
  6. cybertech

    cybertech Retired Moderator

    Joined:
    Apr 16, 2002
    Messages:
    72,115
    Good job, tCoop61 :)
     
  7. tCoop61

    tCoop61

    Joined:
    Aug 5, 2003
    Messages:
    47
    Continued: - sorry, just re-read the initial post:

    Then, copy that formula down beside your list of names, select the data column and the new title column, and do a Sort based on the titles coulmn.
     
  8. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    For Coop:

    =RIGHT(B4,LEN(B4)-(FIND(" ",B4,(FIND(" ",B4)+1))))

    There. Now it's pretty. Tho I personally thought it was gorgeous just the way it was.

    :D
     
  9. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> assuming its always the 3rd word
    >> (followed by the second space):

    Darn! Used to have something that used a built-in function to reverse a text string ; but seems to be lost, so I had to go looking again.

    =RIGHT(A1,LEN(A1)-MAX(ROW(INDIRECT("1:"&LEN(A1)))
    *(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ")))

    will give you everything after the last space, regardless of # of spaces.

    NB - array formula ; use CTRL+Shift+Enter, *not* plain Enter.

    If you're interested in the reverse thing, try this UDF from John Walkenbach (enter in a plain VBA module, then access via Paste Function) -

    Function Reverse(InString) As String
    Dim i As Integer
    Dim StringLength As Integer
    Reverse = ""
    StringLength = Len(InString)
    For i = StringLength To 1 Step -1
    Reverse = Reverse & Mid(InString, i, 1)
    Next i
    End Function
     
  10. ckaas

    ckaas Thread Starter

    Joined:
    Feb 3, 2003
    Messages:
    133
    cheers all! thats exactly what i needed!
    fantastic.

    Christian K.
     
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/167619

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice