Backwards Sort

  • I would like to have Excel sort a list of numbers or text by looking at each number/text item from right to left instead of left to right to determine the order.


    Example:


    Unsorted List
    63419727
    13536225
    41228128
    19551323


    Sorted List
    19551323
    13536225
    63419727
    41228128


    Does anyone have a suggestion on how this could be done?

  • Welcome to the forum.


    Here's a formula to reverse the order of your 8 digit number. Assumes the number is in B8.


    =VALUE(RIGHT(B8,1)&MID(B8,LEN(B8)-1,1)&MID(B8,LEN(B8)-2,1)&MID(B8,LEN(B8)-3,1)&MID(B8,LEN(B8)-4,1)&MID(B8,LEN(B8)-5,1)&MID(B8,LEN(B8)-6,1)&MID(B8,LEN(B8)-7,1))


    HTH

    There are three types of people in this world.
    Those who can count and those who can't.

  • Just a quick caviot: Neale's formula will actually give you an 8 digit number that is the reverse order of the last 8 digits of you original number. However, if the original number if less than 8 digits it will return an error because soem of the MID functions canot find their target.


    If all of your numbers are 8 digits or longer it should work just fine. If they vary in length, then the fewer numbers you reverse, the less likely you'll get an error, but the more likely that you will get duplicates in the reverse-order sort colume.

  • Thanks for the help Neale and Thomach. This gives me something to start with. I do have some alpha-numeric entries that are 9 characters. I will try to incorporate Neale's suggestion into a macro that will reverse each number in a selected range, sort the range, and then reverse the numbers back to their original order. If I run into trouble, I'll probably be back. Thanks again.

  • This is a slight adaptation of a usere defined function macro by John Walkenbach (distributed with his book "Excel 2000 Power Programming with VBA" --- John's macro reverses cells that are all text). This adaptation doesn't care.


    If you sort on the second colume (the new colume you create with the function) you will still have your original "numbers" in the first colume, so there is no need to recreate them.


    Hope this helps.

  • Addendum:


    After I posted the macro previous, I got to wondering (I had not tested it, what would happen if the original cell entry was not a fixed-length number (e.g., 1/3 or RAND(). The function does work just fine (the attached is the same macro but I added a few extra cells in Colume A to test things -- some of the values change everytime the sheet calculates) but it strikes me that if you have any numbers that are long rational numbers tructcated at some point only by Excel's limit to the nmber of decimals it carries, then your sort on its reverse may not give you the result you are after.


    If all of you entries are discrete, fixed length entries, then this will not be a factor.

  • I'm finally getting a chance to get back to this sort problem. When I first posted this I overlooked an important requirement of this sort: the numbers need to be reversed in groups of two. For example, 13528973 must become 73895213 rather than 37982531. It might be easier to visualize this if I separate the numbers:


    13-52-89-73 becomes 73-89-52-13


    This is further complicated by the fact that I have some 9-digit alphanumeric entries mixed in with the 8-digit numeric ones. Thus, 0876K8021 must become 21806K870. If I can't incorporate these into the function, I can live with it.


    To reiterate all the needed steps, first the numbers must be reversed as in the above example, then sorted, then reversed back again (I do not want two columns of numbers).


    I tried incorporating thomach's function into a VBA module, but since I'm an absolute novice with VBA (when MS moved from Excel's macro language to VBA, I never got around to learning it) I couldn't get the module to recognize the function call. After I've played around with it more, if I still can't get it to work, I'll post the code.


    Thanks for the help!

  • try this copy your column to another column.


    Run this macro on it - it does your reversal - though I don't know how youwant to handle leading zeroes.


    It doesn't change 9 digits cells. You can then do the 9 digits manually or I could do something if you can give some rules on where the letter could appear and how to handle it. Sort the list and then delete the column


    Sub RevSortOf()


    Dim Num
    Dim NewNum


    For Each Num In Selection


    If Len(Num) = 8 Then

    NewNum = Mid(Num, 7, 2) & Mid(Num, 5, 2) & Mid(Num, 3, 2) & Mid(Num, 1, 2)

    Num.Value = NewNum

    End If


    Next Num


    End Sub


    HTH

    There are three types of people in this world.
    Those who can count and those who can't.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!