+ Reply to Thread
Results 1 to 6 of 6

Excel 2007 : Complex alphanumeric sorting

  1. #1
    Registered User
    Join Date
    12-29-2010
    Location
    NY
    MS-Off Ver
    Excel 2007
    Posts
    35

    Question Complex alphanumeric sorting

    Hi there, new here. :D

    I've read through some of the other alphanumeric sorting posts but nothing seemed to help with my problem. I have something like this:

    a1402
    a1a21
    a1235
    z124
    x1x2
    x11a2

    Not only are the letters and numbers mixed, but also of different lengths.

    What it should sort as is:

    a1a21
    a1235
    a1402
    x1x2
    x11a2
    z124

    But what it is doing is this:

    a1235
    a1402
    a1a21
    x11a2
    x1x2
    z124

    aia should be BEFORE a12 and a14 etc...

    I can make the cells all the same length by inserting numbers at the end but I can't get it to sort this right. Assuming I have hundreds more rows in my project so it's not so simple as manually reorganizing, is there anything I can do? Thnks!

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Complex alphanumeric sorting

    Is the "prefix" always a single character or can it vary - eg xx1 etc...

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Complex alphanumeric sorting

    I am heading off line shortly - or at least won't be posting much so figured I'd add something.

    Based on your examples whereby alphas are only ever single char I think the "simplest" thing is to create a new key for sorting based on a split of the originals.

    If we assume your values listed are in A1:A6 then

    Please Login or Register  to view this content.
    we can then combine B:E into a new key which we can use as sort key

    Please Login or Register  to view this content.
    not very elegant I grant you...
    in above we have assumed # are always <= 99999999

  4. #4
    Registered User
    Join Date
    12-29-2010
    Location
    NY
    MS-Off Ver
    Excel 2007
    Posts
    35

    Re: Complex alphanumeric sorting

    So far they seem to be single letters. Thanks for your help. I think I’m a bit more of a basic user than this though, where do I enter all of that? Surely it won’t all go into the formula bar? Can you give me a bit more of a step by step? Thanks!

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Complex alphanumeric sorting

    Attached is a working example... it is as you state a non trivial exercise.

    That being said others may have better ideas for you to pursue.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    12-29-2010
    Location
    NY
    MS-Off Ver
    Excel 2007
    Posts
    35

    Re: Complex alphanumeric sorting

    Thanks for your help! I finally figured out how to get it to work and it works beautifully.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1