Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting alphabetically (XP SP2)

    Good afternoon

    I have just spent several hours re-arranging a list to get it into alphabetical order (I can hear you all shouting now, USE THE SORT BUTTON) because the sort button does not arrange it alphabetically for me. I have a list of 2638 UK postcodes kind of like

    B1
    B2
    B3
    B4
    Through to B99

    When I use the sort button it arranges them out of order, for example

    B1
    B10
    B11 etc. to
    B19
    B2
    B20

    I have just manually changed all of them by inserting rows, cutting, pasting and deleting rows and then saved it, however when I have opened it again it has re-arranged them.

    My scalp is bleeding from pulling out my hair, any suggestions please (I cannot have them showing B01, B02 etc.)

    Thanks

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sorting alphabetically (XP SP2)

    Steve

    This is a brainstorm moment but how about adding another (hidden?) column with this formula:

    =IF(LEN(A1)=2,LEFT(A1,1)&0&RIGHT(A1,1),A1)


    and copy down, you can copy | paste special | value in the real coulmn and the delete.....just an idea
    Jerry

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting alphabetically (XP SP2)

    Sort is sorting the column into the correct order. "1" comes before "2" in the sorting order, and therefore all of the 1's in the second position will come before the 2's etc.

    You could insert another column in the data, lets say column B. If your "postcodes" start in A1, then put this formula into B1:

    <code>
    =LEFT(A1,1)&TEXT(MID(A1,2,99),"00")
    </code>

    Fill this formula down as far as the "postcodes" go. Now sort both columns on column B.
    Legare Coleman

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Sorting alphabetically (XP SP2)

    If you are going to be doing this frequently consider making a custom list and sorting by it. Briefly:

    - to create the list, drop a complete list of possible postal codes in an Excel column then use Tools | Options | Custom Lists to save the list.

    - to sort it, select Data | Sort, click the Options button and in the dropdown in First Key Sort Order select your custom list as the sort mode.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting alphabetically (XP SP2)

    Thanks all for the responses

    I must be losing it somewhere though, in the attached sample workbook column A represents the original figures, column B using Jezza's formula and column C using Legare's, however the sort always seems to come out the same?

    It is obviousely me but I can't see why

    Cheers

    Steve
    Attached Files Attached Files
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting alphabetically (XP SP2)

    Thanks John I will try this when I get this column sorted (I just had to do it, did'nt I)

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting alphabetically (XP SP2)

    The sorting doesn't work because you provided incorrect information. The entries do not consist of ONE letter followed by a number but of TWO letters followed by a number. You must modify the formulas to take that into account. See attached version
    Attached Files Attached Files

  8. #8
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting alphabetically (XP SP2)

    They are sorting the same way because the data structure on your spreadsheet is different than your original post Spreadsheet is 2 alpha characters and then the number, while your sample data is one alpha character and then the number. If the number of alpha characters are going to be different lengths, you'll probably need a UDF.

  9. #9
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting alphabetically (XP SP2)

    Thanks and sorry

    I made the assumption that people were familiar with UK postcodes!!

    2 problems though that you may be able to help me with on this

    1. Sorting your example returned AL1 as AL01, AL2 as AL02 etc. which I can't have
    2. Some UK postcodes are 1 alpha 2 numeric, some are 2 alpha 1 numeric or 2 alpha 2 numeric (B1,B10,AL1,AL10 etc.)

    Is ther any way that this can be worked around.

    Thanks as usual

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  10. #10
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting alphabetically (XP SP2)

    Thanks mbarron

    Hans beat you to the punch by 1 minute

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting alphabetically (XP SP2)

    1. The extra column is only meant for sorting, not for display. You'd keep the original column for display.

    2. You see how important it is to provide complete and correct information. You cannot expect readers to know and understand the arcane UK post code system, probably the least logical system in existence.

    You could use this array formula in B1 and fill down:

    =MID(A1,1,MATCH(FALSE,ISERROR(1*MID(A1,ROW($1:$10) ,1)),0)-1)&TEXT(1*MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW( $1:$10),1)),0),255),"00")

    Again, this is only for sorting!

    See attached version.
    Attached Files Attached Files

Posting Permissions

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