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
"postitnote" wrote in message
...
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.
"Ragdyer" wrote:
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
|