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!

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 wrote:

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

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

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

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 wrote:

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

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

--

Dave Peterson

Thanks. I'll give it a try.

"Dave Peterson" wrote in message
...
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 wrote:

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

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

--

Dave Peterson