Sorting Problems

[Brenda Rueter]

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!

 

[Dave Peterson]

If you inserted a new column and added a formula to treat each entry as text,
would that get you closer.

If your data is in A2:Axxx then insert a new column B and put this in B2:

=a1&""

And drag down the column.

Now try sorting it.

 

[Brenda Rueter]

That got us closer. All the blank cells sorted to the top, as I would
expect. However, we still have a problem with the numbers because the # of
digits is not consistent. We got:
40020
4100xxxM
41010

I would expect the long numbers would be at the bottom. Any ideas?

 

[Dave Peterson]

It's not the length of the value in the cells. It's just like regular English
words.

Super
comes before
Superman

Are all the letters at the far right of the values--no numbers to the right.

If yes, then maybe just splitting the values into two columns -- the first one
with the numbers (treated as numbers) and the second one with the text.

If your data is in column A, then insert two new helper columns.

Put this in B1:
=--MID(A1,MATCH(FALSE,ISERROR(-MID(A1,ROW(INDIRECT("1:100")),1)),0),
100-SUM(--ISERROR(-MID(A1,ROW(INDIRECT("1:100")),1))))

Put this in C1:
=MID(A1,LEN(B1)+1,200)

And copy them both down.

Now sort using those two helper columns.

 

[Brenda Rueter]

Thanks. I'll give it a try.

It's not the length of the value in the cells. It's just like regular English
words.

Super
comes before
Superman

Are all the letters at the far right of the values--no numbers to the right.

If yes, then maybe just splitting the values into two columns -- the first one
with the numbers (treated as numbers) and the second one with the text.

If your data is in column A, then insert two new helper columns.

Put this in B1:
=--MID(A1,MATCH(FALSE,ISERROR(-MID(A1,ROW(INDIRECT("1:100")),1)),0),
100-SUM(--ISERROR(-MID(A1,ROW(INDIRECT("1:100")),1))))

Put this in C1:
=MID(A1,LEN(B1)+1,200)

And copy them both down.

Now sort using those two helper columns.