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
|