Sort Order Doesn't Seem Correct

[Rich Locus]

Hello:
I have a program that sorts a text column and then does a sequence check to
make sure it is in ascending order so I can use a Vlookup using the option
where data must be in a sorted order.

Here is a snippet of the data where it doesn't sort correctly. The field is
defined as text. If you copy and paste this into a column you should have
the same strange results in that it doesn't sort it in ASCII order. The dash
character (Hex 2D) sorts AFTER the letter "K", which has a Hex code of "4B".
So it is not sorting correctly and my VLookup fails in that area.

Here's the data... try it for yourself:

1301932-DBKI/OL
1301932-DBKI/OL
1301932-DBKI/OL
1301932-DBKI/OL
1301932KI/OL
1301932KI/OL
1301932KI/OL
1301932KI/OL
1301932-LBKI/OL
1301932-LBKI/OL
1301932-LBKI/OL
1301932-LBKI/OL

The last 4 rows should be higher in the sorted data, not at the end.

Any help would be appreciated.

I have tried to sort using VBA and also just the regular user interface...
with the same results.

 

[Jim Cone]

From the xl2003 help file...
"Default sort orders"...
"Apostrophes (') and hyphens (-) are ignored, with one exception: If two text strings are the same except for a hyphen,
the text with the hyphen is sorted last."

Not the way I would do it, but ...
--
Jim Cone
Portland, Oregon USA
http://www.contextures.com/excel-sort-addin.html




"Rich Locus" <[email protected]>
wrote in message Hello:
I have a program that sorts a text column and then does a sequence check to
make sure it is in ascending order so I can use a Vlookup using the option
where data must be in a sorted order.

Here is a snippet of the data where it doesn't sort correctly. The field is
defined as text. If you copy and paste this into a column you should have
the same strange results in that it doesn't sort it in ASCII order. The dash
character (Hex 2D) sorts AFTER the letter "K", which has a Hex code of "4B".
So it is not sorting correctly and my VLookup fails in that area.

Here's the data... try it for yourself:

1301932-DBKI/OL
1301932-DBKI/OL
1301932-DBKI/OL
1301932-DBKI/OL
1301932KI/OL
1301932KI/OL
1301932KI/OL
1301932KI/OL
1301932-LBKI/OL
1301932-LBKI/OL
1301932-LBKI/OL
1301932-LBKI/OL

The last 4 rows should be higher in the sorted data, not at the end.

Any help would be appreciated.

I have tried to sort using VBA and also just the regular user interface...
with the same results.

 

[Per Jessen]

Hi

It seems as you have also figured out, that the dash confuses the sort
function.

I used Find/Replace and replaced the dash with a space character,
sorted the data, and inserted the dash again using Find/Replace.

Hopes this helps.
....
Per

 

[Rich Locus]

Thanks for the advice. You both answered my question and get credit... Do I
like the way Microsoft did this? NO!!! If I sort the same data in a
Microsoft Access query, it sorts correctly... What were they thinking!!!

 

[Neal Zimm]

Rich, I feel your pain as I had the same problem.

RE: what were thinking, my best guesses are, (if they matter)

1. Since you can use the apostrophe as the first byte in a cell to
"make" numeric data seem like text, MSoft chooses to ignore it when doing a
worksheet sort. Go figure.

2. The dash is harder, but it must be something like this. Excel is
mostly a numbers processing tool and a leading or trailing - is a negative
number.
One WOULD THINK the Redmond crew can differentiate a dash with a numeric
value versus being contained within a non numeric string, but then if my aunt
had balls she'd be my uncle. Also, if it sees 100-25 it could be trying to
figure out that you want 75.


Best,
Neal

 

[Rich Locus]

Thanks, Neal:

I did find out that even though the VLookup and the Match function both say
the data must be in ascending order (they don't give any details in the help
file) , they mean "Microsoft special ascending order". I actually put a
column in true ASCII sequence that included dashes, and the Vlookup and Match
functions failed because columns really CAN'T be in true ASCII ascending
sequence. Apparently both the VLookup and Match functions also have
exceptions for dashes and apostrophes. I wish Microsoft would be more clear
on their documentation, or at least have an option in the SORT, VLOOKUP and
MATCH function that says "Use True ASCII Sequence".

Go figure