Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts

    Have I found a sorting error in Excel?

    I want to sort some values in a column (using a filter and sorting A-Z). But, if there is a minus sign in the data, Excel seems to ignore it. Have a look at this example with 4 cells as

    I expect it to be:
    Code:
    A-1
    A-2
    A1
    A2
    Excel sorts it as:

    Code:
    a1
    a-1
    a2
    a-2
    That screws up some other tests I do.

    So, is it a bug I have to work around or have I done something silly?

    Alan
    Last edited by alan sh; 2013-08-20 at 07:15.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Alan,

    Looks like a bug to me. These values are text and should sort left to right a character at a time. If you consult the character map
    CharMap.JPG
    You'll notice that the - (2nd highlighted character) is less than any Number or Letter! I've also highlighted the ) to show the error.
    Badsort.JPG
    Notice in the above example how the entries with the ) sort correctly where as the ones with the - do NOT as they should sort in postions 3-4 vs 4,6.

    Sorry, but I don't know of any workaround. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    alan sh (2013-08-20)

  4. #3
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    Thanks for confirming it. For now, I've got around the bug by removing all minus signs in the relevant column before I do a sort. It's a pain.

    Alan

  5. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi Alan/RG

    It is NOT a bug:
    When sorting, 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.
    That is how excel does it.

    zeddy

  6. #5
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    Humm - seems like a bug to me (grumble, grumble).

    I can understand apostrophies but minus signs?? Why???

    It played havoc with my comparison algorithm which assumes that data is sorted correctly - I check that a text string is equal to, greater than or less than another one. If one list has minus signs in and the other doesn't, the algorthim goes haywire.

    But never mind, I've now done it differently (but it's not as elegant).

    regards

    Alan
    [I still think it's a bug ]

  7. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi Alan

    Sorry to disappoint you, but as Aldous Huxley said, "Facts do not cease to exist because they are ignored"

    Microsoft explains "How to correctly sort alphanumeric data in Excel" in KB322067.

    To get the sort order you want, you could first replace all dashes ('minus signs') in your source range with , "0^", then sort, then replace all "0^" with "-" to get the data back to original format.

    zeddy
    Last edited by RetiredGeek; 2013-08-20 at 16:44. Reason: Added link to KB article

  8. The Following User Says Thank You to zeddy For This Useful Post:

    alan sh (2013-08-20)

  9. #7
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    OK - I give up....

    Many thanks for the responses.

    regards

    Alan

  10. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi Alan

    Don't give up! We want to help you!
    I tested my proposed solution in post#6 and it works - it gives you exactly your required sort order.
    It would be very simply to incorporate this Sort into an existing vba routine, or to make one.

    zeddy

  11. The Following User Says Thank You to zeddy For This Useful Post:

    RetiredGeek (2013-08-20)

  12. #9
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    Zeddy,

    I did have a smiley....

    I haven't given up. What I have done is created a dummy column at the end of my spreadsheet and created a copy of the name without the minus sign. Once I have finished the pre-processing, I sort the whole spreadsheet on that column which gives me the required sort order. I then do my comparisons on the original data (I need the origical name for recording differences).

    At the end, I then delete that extra column (actually, I don't yet, but I will when it's all debugged).

    It works just fine.

    Many thanks for your continuing assistance. It's nice to have this sort of support.

    regards

    Alan

  13. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You could keep the column using a SUBSTITUTE formula instead and sort on this column. This type of "sort column" also works to "ignore" the starting "The", "A " or "An " in titles. I find it less cluncky than editing the titles to something like: "Great Gatsby, The" ...

    Steve

  14. #11
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    Thanks - but the whole spreadsheet is created by my macro and I have made my own "substitute" function (I didn't know there was one in VB - and I can't find one in the help).

    regards

    Alan

  15. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi Alan

    If you just create a dummy column, without the minus sign, and sort on that, you will NOT get the sort order that you wanted in post#1
    Ditto, if you use Steve's SUBSTITUTE formula to replace the minus signs with 'blanks", you will NOT get the sort order that you wanted in post#1

    see attached file with specific example as per your post#1 sort requirement.

    You can use Steve's SUBSTITUTE formula, provided you use something like my "0^" (see attached file).
    The reason for the "0^" is important: the zero part ensures that A-1 will sort before A1 as you required
    zero 0 is the first sort char used by Excel.
    Specifically:
    Excel sorts data in the following order:
    0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = > A B C D E F ...

    The carat symbol (it could be anything not directly used in your real data, e.g. could use #, ] etc, or even "0^^^^" etc) it is just so that if we were using vba to replace all minus chars, we need something 'unique' so we can then replace it back to the 'minus' sign afterwards.

    zeddy
    Attached Files Attached Files

  16. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi Alan

    ..and just for info, you use REPLACE in vba for SUBSTITUTE

    zeddy

  17. #14
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    Ok - sounds like I have some work to do tomorrow.

    Thanks again

    Alan

  18. #15
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Zeddy,

    FYI, the Thanks in post #8 is really for post #6. I edited post #6 to add a link to the KB article then it wouldn't let me do a Thanks!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •