How can I sort SSNs in Terminal Digit Order using Excel?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I work in a medical records office and we are attempting to combine five
years' worth of records onto one spreadsheet. Our office uses Terminal Digit
Order (or TDO) and it doesn't appear that Excel can sort this way as it is in
order with the last four digits, then the middle two, then the top three.
Does anyone know if there is a way to do this without having to put the SSN
in three separate columns and then merging the columns together somehow?
 
I work in a medical records office and we are attempting to combine five
years' worth of records onto one spreadsheet. Our office uses Terminal Digit
Order (or TDO) and it doesn't appear that Excel can sort this way as it is in
order with the last four digits, then the middle two, then the top three.
Does anyone know if there is a way to do this without having to put the SSN
in three separate columns and then merging the columns together somehow?
Click to expand...

The simplest method would be to add a "helper column" that has the digit groups
in the order to be sorted -- then sort on that column.

If your digit groups are separated by dashes (and are located in column G),
then:

=RIGHT(G1,4)&MID(G1,5,2)&LEFT(G1,3)

will return a text string of the digits in the order you specified.

Include this helper column in your sort table, and sort on that column. You
can then delete or hide the helper column.

If it is going to be a repetitive task, it can be automated using a macro.

If your numbers are NOT separated by dashes, but are rather a sequential
string, then use this formula:

=RIGHT(TEXT(G1,"000-00-0000"),4)&MID(TEXT(
G1,"000-00-0000"),5,2)&LEFT(TEXT(G1,"000-00-0000"),3)


--ron
 
TTC (Text To Columns) can separate out groups from your list of numbers
without bothering (changing - revising) your original column.
Sort on the extracted data, and then throw it away, without even touching
the original list, except to sort it, of course.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
 
An example would be the following:

Say I have five SSNs that I need to put in TDO. These SSNs are:
123-45-6789
987-65-4321
567-89-1234
246-81-3579
192-83-7465

They would be in TDO like this:
987-65-4321
567-89-1234
192-83-7465
246-81-3579
123-45-6789

So in my original post I miswrote that it is the last four, followed by the
next two, followed by the first three. It's actually the last two,
second-to-last two, third-to-last two, then first three.
 
This sounds like a very good idea though after looking at my spreadsheet I
must admit that I'm not sure what to do with this information. What is a
"helper column" and how do I get it? I did input the information you gave me
(=RIGHT(G1,4)&MID(G1,5,2)&LEFT(G1,3)) and noticed how it changed the column,
but it's not exactly what I need (see my reply to the post made by Ken in
this thread).

Thank you for your help.
 
Just repeating, TTC is *perfect* for what you're looking to do.

Select your column of numbers, then,
<Data> <Text To Columns> <Fixed Width> <Next>
Then create 5 break lines, separating your last set of 4 digits in the
middle and then separating out the dashes from the other numbers.
Then click <Next>

Now, change the address in the "Destination" box, to a column where there's
room to print out the 6 columns, and also this allows the original column of
numbers to remain intact, where they were.

Now click <Finish>

You have your original data, with adjoining columns parsed out the way you
separated them.
Now select them all, and sort in any order that you wish.

When you're done, throw away those extra columns.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
 
As I understand it, you want to sort by the last (terminal) digit first.
Regardless of whether I understand this correctly, the way you get Excel to sort
properly is to create a column of cells which are ordered properly (many people
in this board call this a "helper column").

For example, insert a column after your SSN. Use a formula like:

=right(a1,9)&left(a1,8)

or whatever formula will allow Excel to sort the records the way you want. Then
sort on this column. You can hide the column if you don't want it to appear on
any reports.
 
This sounds like a very good idea though after looking at my spreadsheet I
must admit that I'm not sure what to do with this information. What is a
"helper column" and how do I get it? I did input the information you gave me
(=RIGHT(G1,4)&MID(G1,5,2)&LEFT(G1,3)) and noticed how it changed the column,
but it's not exactly what I need (see my reply to the post made by Ken in
this thread).

Thank you for your help.
Click to expand...

A "helper column" is just an extra column that you insert and will use, at
least temporarily, in your table.

Lets say you had the SSN's in Column A and other data related to these SSN's in
Columns B:F.

Click the A which selects the entire column. Then Select Insert from the main
menu and Column from the drop down sub-menu.

Everything moves over one and your SSN's are now in column B; Column A is now
your helper column.

Given your post to Ken, modify the formula I wrote to read:

=RIGHT(B2,2)&MID(B2,8,2)&MID(B2,5,2)&LEFT(B2,3)

Copy/drag this formula down as far as required.

Then select your entire table, including column A, and sort ascending (as
number) on Column A.

Finally, you can delete column A, or hide it.

This can be also done as a macro.



--ron
 
Ok, I tried all of the options given here and this one seems to be the best
solution. My only concern now is that the spreadsheets were given to us set
up as SSNs and Numbers and not as text so whenever I make it text to column
and one of the columns has a "04" in it, then only the four shows up. I have
over 11K names and SSNs on one list (and five lists) so how can I make those
0's appear without having to go thru each and every number to input the 0
manually?

Thank you!
 
Since your *original* data is *untouched*, I don't see what difference that
makes.

You're going to sort on 4 ... right?

You're going to throw it (04, 4)away when you're done anyway!

Perhaps you're missing the original concept of this procedure.
 
No, I understand what you were saying. I didn't try to actually sort once I
saw that the 0's had disappeared. My main concern was if the SSNs started
with 003 or 000 or something like that, it appeared that the whole number
would be completely off when sorted. But I just did a test run and
everything worked fine!

Thanks for your help!!
 
Ok now that I went and wrote that everything worked fine I have to write
again and say it didn't work when I actually put all 45K names together.
Here is what is happening (again, with the 0's)...

I have the following SSNs:
111-11-4030
222-22-0400
333-33-7040
444-44-0500
555-55-0300
666-66-0040

In TDO, they should show up like this:
555-55-0300
222-22-0400
444-44-0500
111-11-4030
666-66-0040
333-33-7040

....but instead they are showing up like this:
111-11-4030
555-55-0300
666-66-0040
333-33-7040
222-22-0400
444-44-0500

....which is not TDO. I believe this is happening because of the 0's that
are disappearing at the end of the SSN, but I don't know for sure. Any
suggestions?
 
Use the helper column with this formula and then select all and sort on the
helper column.

=RIGHT(A2,2)&MID(A2,8,2)&MID(A2,5,2)&LEFT(A2,2)

Assumes your data starts in row 2
 
I just sorted your example numbers, following the instructions that I posted
here, and they sorted *exactly* as you stated that they *should be*.

The procedure works fine!

Say the original numbers are in Column A, and the 6 parsed (separated)
columns are B to G.
B = first 3 numbers
C = dash
D = middle 2 numbers
E = dash
F = first 2 numbers of last set
G = last 2 numbers

After the 6 columns were parsed along side the original data, did you select
*all* columns.
Did you then set the *first* sort key as Column G (last 2 numbers),
*Second* key as Column F,
And *third* sort key as Column D?

It worked perfectly for me!!!
 
I just sorted that same data as well and it also worked for me. There must
be something within the spreadsheets that were given to us that is messing it
up because the last four digits I used in my example were taken directly from
SSNs that were out of order.

One thing is that the SSN is formatted as an SSN (format cells, number,
special, SSN). When I change this to a regular number, the 0's in the
beginning and end disappear.

I just tried your system with some SSNs that began with 0 and this is what
happens:

The SSN is 000-42-0400 for example. When I do Text to Column and it comes
up in the box to put in the dividers, it looks like this:

420400

....because the 0s in the front disappear. I have no choice but to put the
dividers like this:
420 | 40 |

....because that is where the lines go for SSNs that do not have 0s missing
from them. So when it is broken down, it goes into sort boxes that way. So
if I have two SSNs, 000-42-0400 and 123-45-6789, they look like this in the
extra columns:

420 40
123 45 67 89

....and get sorted as such.

I don't mean to be a pain, and your suggestion is great! Can you think of
what could be causing it to do this? It has to be something within the
spreadsheet, the way the cells are formatted, because I get the correct
output when I hand type sample SSNs into a new spreadsheet.

Thank you.
 
To keep from losing the leading zeros, just be sure to format the columns
from GENERAL to TEXT during the Data > TextToColumns procedure.........

Vaya con Dios,
Chuck, CABGx3
 
Select a cell that has leading zeroes, and post back with exactly what the
cell displays, and exactly what the formula bar displays.
 
I can't post an exact cell because they are actual SSNs which is why I always
give obvious examples.

I can tell you that there are two columns which contain the SSNs, one is
titled SSN and the other is MRN (medical record number). The SSN column is
fomatted as a SSN so you just type in the number and hit enter and the dashes
show up automatically. The MRN column is set up as an SSN in Portuguese (do
the same steps to format it as an SSN but when it asks you for location it
says "Portugal") so there are no dashes. When I change either of these
columns to text or number, the 0's at the beginning and end disappear. With
over 11K names per list, there's no way I can go thru and add all the 0's
back in.

So the formula bar for the SSN column reads the same number but without the
dashes and the 0's at the beginning or end, and the formula bar for the MRN
column reads this same number as well.

SSN MRN
000-42-0200 000420200

fx: 420200

Thank you.
 
The reason I asked for what you have in the formula bar, is because twice
you've said that you loose the 0's at the beginning *and the end*.

<<"but without the dashes and the 0's at the beginning or end">>

Your example however, shows that the zeroes at the *end*, stay at the *end*.

So, at least now, we can try something, since it's *only leading* zeroes
that we have to worry about.


This worked for me.
Add a column of text formulas to "pad" the existing numbers with leading
zeroes.
This will yield nine numbers, no dashes.
This makes this "helper" column into "Text" numbers, where you will be able
to retain the zeroes while in the TTC procedure.

Remove the text formulas, leaving just the data behind.

You can then split them with the break lines as we discussed earlier, but
with only 4 columns, since we've eliminated the dashes.
<<<<<<<<<<<<<<<<<<<<<<<
Formatted SSN in Column A

Enter this formula in B1, and *double* click the fill handle, so that the
formula in B1 is *automatically* copied down Column B, as far as there is
data in Column A.

=REPT("0",9-LEN(A1))&A1

NOW, to remove the formulas and leave the data, while you *still* have
Column B selected from the formula copy, right click in the selection and
choose "Copy".
Right click again, and choose "Paste Special".
Click on "Values", then <OK>, then <Esc>.

You now have a column of text numbers that should work perfectly in the TTC
procedure.

Don't forget which columns to make your first, second, and third sort keys.
 
Back
Top