Excel 2007 : Custom Sorting

**brantibrooks** · 08-23-2010, 09:14 AM

I have a large database (6000+ names) and I need to sort them in a unique way. We use Terminal digit SSN to locate people and I need a way to sort this list in that order. For example a SSN is 123456789: The terminal digits are 89. I want them sorted by terminal digit (89) and then by the last four (6789). How would I do this? They need to be in order by terminal digit and within that terminal digit be in numerical order. Thank you in advance for any help.

**NBVC** · 08-23-2010, 09:29 AM

Extract the 2 items in 2 separate new columns...

e.g.

=Right(A2,2)+0 will extract last 2 digits from A2... copied down

=Right(A2,4)+0 will extract last 4 digits from A2... copied down.

then sort by the 2 digit column, then by the 4 digit column.

**brantibrooks** · 08-23-2010, 09:35 AM

I really don't understand any of that. My knowledge of excel is limited. I can do most simple functions, but that is it. Explain it to me as you would somebody who is using excel for the first time, please.

**NBVC** · 08-23-2010, 09:44 AM

if you have your list in column A, starting at cell A2, then enter in a free column, row 2...

=Right(A2,2)+0

this formula extracts the last to digits from whatever is in A2. Replace A2 with whatever cell reference contains the first SSN.

Copy that formula down, but selecting the cell you entered it in. Click and drag down the little black square at the bottom right corner of the cell until you get to the bottom of the list.

Then in another free column (again in row 2.. assuming your list starts in row 2)..enter

=Right(A2,4)+0 again change A2 if that is not where your first SSN number is... and copy down as above.

Now, select the entire range, including these 2 new columns and go to Data|Sort and select the column you entered the first formula in as your first sort, and the other formula column as your second sort.

Choose Ascending or Descending based on what order you want and click Ok.

Hope that explains it.