Need Help with Sorting Problems
 

This originally got buried under someone's February post so please forgive
the reposting...

User has Excel 2000. This spreadsheet has a column identified as an ID# but
the numbering is not consistent. The majority within the data are 5-digit
numbers. There are some that start with a leading zero. There are also
some 10+ digit numbers which have letters in them, generally at the end.
There are even some records that do not have any ID and that field is blank.
So far we have not been able to get a good sort out of the data.

The sort first gives us the 11xxx numbers, followed by 2 through 8 5-digit
numbers, then followed by the 5-digit numbers with a leader zero. Then it
gives some of the 10+ digits #s which include a letter within a sort order
starting with the first number (e.g., 123485767GH, 135679848B, 348674798HH,
etc.). Then some 10xxx numbers start up again and we start numbering all
over. The blank cell rows are sorted at the END of the document when I
would think they would have been put at the top.

I have copied entire sheet to new document and pasted as special values. I
have copied just this column of data as well as another column 100%
populated with data and pasted special values. I have formatted the column
to text and as number. The data continues to sort in the same incorrect
fashion.

The puzzler is that it does sort 100% correctly in Excel XP. So, can
anybody suggest anything to try in Excel 2000 or is the only answer for the
user to be upgraded? TIA!

Brenda,

Excel sorts numbers to the top, followed by text,
followed by blanks.
So 9876 (a number) comes before 12345A (text).

Excel 2002 has the capability to sort text that looks
like numbers as numbers.

You should be able to sort the data the way you want by
converting all of the data to text and then sorting it.
This can be quickly accomplished by selecting the sort
column and using... Data | Text to Columns.
In the "Wizard" that appears, use the defaults,
except for Step 3 where you should check the "Text" button.

You should now be able to sort sequentially.

Regards,
Jim Cone
San Francisco, USA


"Brenda Rueter" wrote in message ...
This originally got buried under someone's February post so please forgive
the reposting...
User has Excel 2000. This spreadsheet has a column identified as an ID# but
the numbering is not consistent. The majority within the data are 5-digit
numbers. There are some that start with a leading zero. There are also
some 10+ digit numbers which have letters in them, generally at the end.
There are even some records that do not have any ID and that field is blank.
So far we have not been able to get a good sort out of the data.
The sort first gives us the 11xxx numbers, followed by 2 through 8 5-digit
numbers, then followed by the 5-digit numbers with a leader zero. Then it
gives some of the 10+ digits #s which include a letter within a sort order
starting with the first number (e.g., 123485767GH, 135679848B, 348674798HH,
etc.). Then some 10xxx numbers start up again and we start numbering all
over. The blank cell rows are sorted at the END of the document when I
would think they would have been put at the top.
I have copied entire sheet to new document and pasted as special values. I
have copied just this column of data as well as another column 100%
populated with data and pasted special values. I have formatted the column
to text and as number. The data continues to sort in the same incorrect
fashion.
The puzzler is that it does sort 100% correctly in Excel XP. So, can
anybody suggest anything to try in Excel 2000 or is the only answer for the
user to be upgraded? TIA!