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

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?

Give us some examples of your data, and how you want it all to sort.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"postitnote" <postitnote@discussions.microsoft.com> wrote in message
news:9B495656-9171-4F6E-9F57-812BBB66195F@microsoft.com...
> 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?

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.



"Ken Wright" wrote:

> Give us some examples of your data, and how you want it all to sort.
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> ----------------------------------------------------------------------------
> It's easier to beg forgiveness than ask permission :-)
> ----------------------------------------------------------------------------
>
> "postitnote" <postitnote@discussions.microsoft.com> wrote in message
> news:9B495656-9171-4F6E-9F57-812BBB66195F@microsoft.com...
> > 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?
>
>
>

On Tue, 7 Jun 2005 12:30:02 -0700, "postitnote"
<postitnote@discussions.microsoft.com> wrote:

>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?

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

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.



"Ron Rosenfeld" wrote:

> On Tue, 7 Jun 2005 12:30:02 -0700, "postitnote"
> <postitnote@discussions.microsoft.com> wrote:
>
> >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?
>
> 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
>

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!
==============================================

"postitnote" <postitnote@discussions.microsoft.com> wrote in message
news:D312381B-518B-4CC8-803A-D6493D7D9511@microsoft.com...
> 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.
>
>
>
> "Ron Rosenfeld" wrote:
>
> > On Tue, 7 Jun 2005 12:30:02 -0700, "postitnote"
> > <postitnote@discussions.microsoft.com> wrote:
> >
> > >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?
> >
> > 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
> >

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!



"RagDyer" wrote:

> 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!
> ==============================================
>
> "postitnote" <postitnote@discussions.microsoft.com> wrote in message
> news:D312381B-518B-4CC8-803A-D6493D7D9511@microsoft.com...
> > 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.
> >
> >
> >
> > "Ron Rosenfeld" wrote:
> >
> > > On Tue, 7 Jun 2005 12:30:02 -0700, "postitnote"
> > > <postitnote@discussions.microsoft.com> wrote:
> > >
> > > >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?
> > >
> > > 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
> > >
>
>
>

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.
--
Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

"postitnote" <postitnote@discussions.microsoft.com> wrote in message
news:9EE9AF0A-8E2F-4F86-9CCA-F14B12ED0FC2@microsoft.com...
> 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!
>
>
>
> "RagDyer" wrote:
>
> > 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!
> > ==============================================
> >
> > "postitnote" <postitnote@discussions.microsoft.com> wrote in message
> > news:D312381B-518B-4CC8-803A-D6493D7D9511@microsoft.com...
> > > 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.
> > >
> > >
> > >
> > > "Ron Rosenfeld" wrote:
> > >
> > > > On Tue, 7 Jun 2005 12:30:02 -0700, "postitnote"
> > > > <postitnote@discussions.microsoft.com> wrote:
> > > >
> > > > >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?
> > > >
> > > > 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
> > > >
> >
> >
> >

On Tue, 7 Jun 2005 15:50:04 -0700, "postitnote"
<postitnote@discussions.microsoft.com> wrote:

>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.
>

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

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!
==============================================

"postitnote" <postitnote@discussions.microsoft.com> wrote in message
news:9B495656-9171-4F6E-9F57-812BBB66195F@microsoft.com...
> 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?

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.

--
Regards,
Fred
Please reply to newsgroup, not e-mail


"postitnote" <postitnote@discussions.microsoft.com> wrote in message
news:9B495656-9171-4F6E-9F57-812BBB66195F@microsoft.com...
>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?