B
Brenda Rueter
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!
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!