Link to home
Start Free TrialLog in
Avatar of elepil
elepil

asked on

Data won't sort in Microsoft Excel, please help

I have attached an Excel file (and its CSV equivalent) containing just one column and nine rows of data. For some reason, I can't get it to alphabetically sort.

Can anyone give this a shot and tell me if you can sort it alphabetically? And if not, can anyone tell me why?Won-tSort.csvWon-tSort.xlsx

The data inside the file is simply:

JBL-CWT128-WH
JBL-CWT128-WRC
JBL-CWT128-WRX
JBLD-124-67001-00X
JBLD-124-67001-01X
JBLD-129-20010-00
JBL-D16R2445
JBL-D16R2450
JBLD-2241H

For some reason, I cannot make this sort properly with Excel.
Avatar of ☠ MASQ ☠
☠ MASQ ☠

Highlight Sheet > Sort&Filter >  Custom Sort - My Data has Headers
Sort by >  Inventory ID > Values A to Z > OK



Inventory ID
JBL-CWT128-WH
JBL-CWT128-WRC
JBL-CWT128-WRX
JBLD-124-67001-00X
JBLD-124-67001-01X
JBLD-129-20010-00
JBL-D16R2445
JBL-D16R2450
JBLD-2241H

FWIW, Excel sees these as
JBLC...
JBLC...

JBLC...

JBLD...

JBLD...

JBLD...

JBLD...

JBLD...

JBLD...


...completely ignoring the hyphens.  In its own way, Excel has put them in alphabetical order.


Avatar of elepil

ASKER

MASQ, it isn't sorted properly. Did you notice?
Avatar of elepil

ASKER

Well, that is not right. How do I make it not ignore the hyphen?
You don't need to, if you retype the entries you'll find they sort correctly, suspect that hyphen isn't always the same character
The Sheet2 data are sorting either ascending or descending order as though the hyphens aren't there. According to David McRitchie's "rules for sorting" this behavior is the way Excel sorting has always worked.

Do you have a strong reason for needing to sort differently than the way Excel wants to?
Avatar of elepil

ASKER

Yes, the hyphen is the same. I went as far as printing out the ascii values of each character to make sure there were no hidden control characters.

I did retype them all manually (out of desperation), it still didn't sort properly.

If you create a second column and apply this formula...


   =SUBSTITUTE(A2,"-"," ")


...you can sort on that second column and the order will be what I believe you're expecting.  A2 is the matching cell, of course, and you'll need to copy/paste that formula to match all the affected cells.  

If you need me to, I can re-upload your spreadsheet with the solution.

Avatar of elepil

ASKER

MASQ,

"The Sheet2 data are sorting either ascending or descending order as though the hyphens aren't there. According to David McRitchie's "rules for sorting" this behavior is the way Excel sorting has always worked.

Do you have a strong reason for needing to sort differently than the way Excel wants to?"


Umm... in this case, isn't it obvious? I want all the JBL- sorted together, and all the JBLD- sorted in its own group. My dataset actually contains 800+ JBL- entries, and almost a hundred JBLD- entries. I can't have it getting mixed up like the way it's happening.

It is incredible to me that this is the way Excel really does it's sorting, are you serious? Excel is totally disregarding ascii characters on its own? Does it have a reason to disregard hyphens??? I feel like I'm hearing something out of this world.
Avatar of elepil

ASKER

Oh sorry, MASQ, I think I sent you a response that was meant for byundt.
Avatar of elepil

ASKER

I feel surreal. Since when it is okay to disregard a hyphen during sorting? And from Microsoft, of all companies? I feel like I'm living in a different reality.

I have been sorting data for decades programmatically. You *never* ignore anything and always respect ASCII values. And somehow, I am hearing for the first time (and you all seem to be unanimous) that Microsoft candidly chose to ignore the hyphen and does not provide a flag to make the sort NOT ignore the hyphen. Am I understanding this correctly??

Can't say if the solution will work for you, but it sorts the way I think you want it to.


Won-tSort.xlsx

ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of elepil

ASKER

The person who brought this to attention is actually a layman who doesn't know how to use the string handling formulas. I can do all kinds of string/substring substitutions to make this work (like what byundt was suggesting), but I can't expect a layman to have to go through all that rigmarole just to sort a simple list.

I normally admire and respect Microsoft a great deal, but this is an extremely stupid decision they made to just ignore hyphens without providing a sort mode that sorts by ASCII values. Really, really stupid.

I gave byunt more points because he did go an extra step to provide a solution.

Thank you all for your help.
Here's a macro you can use that does something like what byundt did.

Sub SortWithHyphens()

    ActiveSheet.Columns("A").Replace What:="-", Replacement:="|"
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A2:M463")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveSheet.Columns("A").Replace What:="|", Replacement:="-"

End Sub

Open in new window

Avatar of elepil

ASKER

Thanks for your response, Martin. Unfortunately I already closed this ticket.

The person I'm trying to help doesn't even know how to use formulas, much less VBA scripts.

I thank you though for responding.
@Martin, just trying with various other characters, what I am seeing is that | (ASCII Char 5) doesn't sort either.

See below for what I am seeing, the numbers are the range of ASCII codes and whether they sort:

1      to      9      No      
10      to      13      Yes      
14      to      31      No      
32      to      38      Yes      
39      to            No      '
40      to      44      Yes      
45      to                  -
46      to      47      Yes      
48      to      57      Yes      Numbers
58      to      64      Yes      
65      to      90      Yes      Upper Case
91      to      96      Yes      
97      to      122      Yes      Lower Case
                        
150      to            No      –
151      to            No      —
elepil, it looks like I forgot to attach this. Just have your user click the blue button.
29172948.xlsm