Sort Order Doesn't Seem Correct
Rich, I feel your pain as I had the same problem.
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
--
Neal Z
"Rich Locus" wrote:
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!!!
--
Rich Locus
Logicwurks, LLC
"Per Jessen" wrote:
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
On 23 Maj, 06:31, Rich Locus
wrote:
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.
--
Rich Locus
Logicwurks, LLC
.
|