Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   Sorting by surname on excel (https://www.askmehelpdesk.com/showthread.php?t=270375)

  • Oct 17, 2008, 08:58 AM
    Apologisenow
    Sorting by surname on excel
    How do I sort data by surname on excel? I have office 2007
  • Oct 17, 2008, 10:50 AM
    JBeaucaire

    Can you give or post a an example of how you'd like that to work? Without any formulas, just create some cells that show some sample data, then show how you want it to look after.

    Then I'll have an idea.

    Also, is this a one-time thing you want to accomplish, or something that has to keep sorting over and over again because the list keeps changing?

    You can watch this video and it will show you a cool manual way to do it, but it's not automated.
    Sort A List By Last Name
    (be sure to start the video in the center of the page)
  • Oct 17, 2008, 10:53 AM
    ScottGem

    If you have a table of names, You can highlight the table and select the Sort option on the ribbon. You can then choose to sort on selected columns in sequence.
  • Oct 17, 2008, 11:12 AM
    JBeaucaire
    Another manual way (based on an answer I've given elsewhere on a similar topic):

    1. Insert a blank column to the right of your name column. Let's assume the names are currently in the A column.
    2. In the new empty cell at B1, enter this formula:
      Quote:

      =RIGHT(A1,LEN(A1)-FIND("<^>",SUBSTITUTE(A1," ","<^>",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),1))
    3. The last name from cell A1 should appear
    4. Copy the formula down and the last names should appear.
    5. Sort the sheet by this new column
  • Oct 17, 2008, 11:13 AM
    JBeaucaire
    Quote:

    Originally Posted by ScottGem View Post
    If you have a table of names, You can highlight the table and select the Sort option on the ribbon. You can then choose to sort on selected columns in sequence.

    Scott, I believe his problem is the First/Middle/Last names are all in ONE cell. That's why he needs a formula like the one I suggested and a "helper column" to accomplish it.
  • Oct 17, 2008, 12:16 PM
    Apologisenow
    Nice to speak to you again JB. Thanks Scott for your answer but I think JB knows what I am saying. I have a list of names in column A it includes the first name and surname. However I want to sort by the surname so I think JB's answer is correct. I will try shortly.
  • Oct 17, 2008, 12:23 PM
    Apologisenow
    I have done it however a lot of spaces have been left, it only seems to work for some.
  • Oct 17, 2008, 12:30 PM
    JBeaucaire

    Post some examples of the data in the cells it fails for. You could even email me the sheet, if you're a trusting sort. I think I've earned my stripes, you know?
  • Oct 17, 2008, 12:41 PM
    ScottGem
    Quote:

    Originally Posted by JBeaucaire View Post
    Scott, I believe his problem is the First/Middle/Last names are all in ONE cell. That's why he needs a formula like the one I suggested and a "helper column" to accomplish it.

    Ahh, well that needed to be spelled out. I generally use 5 columns (or database fields) for person names:

    Salutation (Mr, Ms, etc.)
    First
    MI
    Last
    Suffix (Jr, Sr, etc.)

    It's a lot easier to concatenate fields together than break them apart.
  • Oct 17, 2008, 02:01 PM
    JBeaucaire
    Quote:

    Originally Posted by ScottGem View Post
    Its a lot easier to concatenate fields together than break them apart.

    Agreed.

  • All times are GMT -7. The time now is 04:55 AM.