Ask Experts Questions for FREE Help !
Ask
    Apologisenow's Avatar
    Apologisenow Posts: 295, Reputation: 2
    Full Member
     
    #1

    Oct 17, 2008, 08:58 AM
    Sorting by surname on excel
    How do I sort data by surname on excel? I have office 2007
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #2

    Oct 17, 2008, 10:50 AM

    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)
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #3

    Oct 17, 2008, 10:53 AM

    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.
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #4

    Oct 17, 2008, 11:12 AM
    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:
      =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
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #5

    Oct 17, 2008, 11:13 AM
    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.
    Apologisenow's Avatar
    Apologisenow Posts: 295, Reputation: 2
    Full Member
     
    #6

    Oct 17, 2008, 12:16 PM
    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.
    Apologisenow's Avatar
    Apologisenow Posts: 295, Reputation: 2
    Full Member
     
    #7

    Oct 17, 2008, 12:23 PM
    I have done it however a lot of spaces have been left, it only seems to work for some.
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #8

    Oct 17, 2008, 12:30 PM

    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?
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #9

    Oct 17, 2008, 12:41 PM
    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.
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #10

    Oct 17, 2008, 02:01 PM
    Quote Originally Posted by ScottGem View Post
    Its a lot easier to concatenate fields together than break them apart.
    Agreed.

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Sorting things out [ 1 Answers ]

How old were you when you met... was this the first girl? What caused you to feel she may not be the one and what about your new friend attracts you please answer and I will do my best to help

Sorting strings alphabetically [ 3 Answers ]

I'm making a source code for the program that sorts strings alphabetically in any sorting techniques(either quicksort,mergesort,heapsort or radix sort).. I will really appreciate if you can help me.. even just the program in sorting the strings in either the mention sorting techniques above.....

Sorting and Sum in Excel [ 2 Answers ]

I'm trying to setup an excel spreadsheet to find the total dollar amount of comps given for several different vendors all out off one mass mixed up spreadsheet. I want to search for a specific name and then get a sum of the dollar amount in the cell to the right of it, over the entire list. Any...

Sorting data [ 2 Answers ]

hi there, another excel question. So I have 2 columns of interst in my spreadsheet. One column is a modulus of a running time score (all values range from 0-2000). The other column is a series of 0's, and some other numbers not of interst. I am interested in all of my modulus values that have 0...

Better MP3 software/sorting solution [ 1 Answers ]

Hi there... When I listen to CDs, I choose the CD by the cover art, but when I'm listening to MP3s its only after I hear it that I can see the cover art... Also, when I listen to CDs I have them shuffled all over the place.. not grouped by genre, but dynamically thrown into various piles and...


View more questions Search