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!!!
--
Regards,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"postitnote" wrote in message
...
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?
"RagDyer" wrote:
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" wrote in message
...
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" wrote in message
...
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"
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
|