| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort Order Doesn't Seem Correct
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sort Order Doesn't Seem Correct
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" 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. -- Rich Locus Logicwurks, LLC |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sort Order Doesn't Seem Correct
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sort Order Doesn't Seem Correct
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 . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sort Order Doesn't Seem Correct
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 :) -- Rich Locus Logicwurks, LLC "Neal Zimm" wrote: 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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sort sheet based on particuilar sort order | Excel Worksheet Functions | |||
| correct order of months in pivot table | Excel Discussion (Misc queries) | |||
| How do I get the correct order for nested subtotals | Excel Discussion (Misc queries) | |||
| Pls. reply Sort Data and copy to next coulmn when sort order chang | Excel Programming | |||
| Making Excel Calculate In Correct Order | Excel Discussion (Misc queries) | |||
Linear Mode
