|
|
|
|
Full Member
|
|
Oct 17, 2008, 08:58 AM
|
|
Sorting by surname on excel
How do I sort data by surname on excel? I have office 2007
|
|
|
Software Expert
|
|
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)
|
|
|
Computer Expert and Renaissance Man
|
|
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.
|
|
|
Software Expert
|
|
Oct 17, 2008, 11:12 AM
|
|
Another manual way (based on an answer I've given elsewhere on a similar topic):
- Insert a blank column to the right of your name column. Let's assume the names are currently in the A column.
- In the new empty cell at B1, enter this formula:
=RIGHT(A1,LEN(A1)-FIND("<^>",SUBSTITUTE(A1," ","<^>",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),1))
- The last name from cell A1 should appear
- Copy the formula down and the last names should appear.
- Sort the sheet by this new column
|
|
|
Software Expert
|
|
Oct 17, 2008, 11:13 AM
|
|
Originally Posted by 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.
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.
|
|
|
Full Member
|
|
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.
|
|
|
Full Member
|
|
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.
|
|
|
Software Expert
|
|
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?
|
|
|
Computer Expert and Renaissance Man
|
|
Oct 17, 2008, 12:41 PM
|
|
Originally Posted by JBeaucaire
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.
|
|
|
Software Expert
|
|
Oct 17, 2008, 02:01 PM
|
|
Originally Posted by ScottGem
Its a lot easier to concatenate fields together than break them apart.
Agreed.
|
|
Question Tools |
Search this Question |
|
|
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
|