1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel - function to sort multiple columns of alphanumeric data

Discussion in 'Business Applications' started by holly00, Jul 7, 2010.

Thread Status:
Not open for further replies.
  1. holly00

    holly00 Thread Starter

    Joined:
    Jul 27, 2007
    Messages:
    26
    Hi all,
    I have a spreadsheet that holds sports results based on age groups.

    The age groups are in one column stored as U6B, U6G, U7B, U7G...U17B, U17G (which is the age + B or G boy/girl)
    The results are in another column stored as whole numbers

    I am trying to find a formula that will allow me to sort on the first column (in the order shown above), and then on the second column in descending order, and then bring back the other data like the name.

    example:
    Child A U15B 1000
    Child B U6G 500
    Child C U6B 700
    Child D U6G 900
    Child E U7G 800

    would be returned as

    U6B Child C 700
    U6G Child D 900
    U6G Child B 500
    U7G Child E 800
    U15B Child A 1000

    I know I can use a manual sort but I was hoping to automatically put the results in a separate sheet in the correct order.

    Any ideas? Thanks in advance.
     
  2. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    You could easily enter in this formula:
    =IF(LEN(B1)=3,LEFT(B1,1)&"0"&RIGHT(B1,2),B1)
    (where the three data columns are just A, B, and C) so that you get - instead of U6B, for instance - U06B. This allows you to sort correctly by age.
    If you want it copied and sorted to another page like that, you really need a macro/VBA.
     
  3. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Put this into a regular module and run it and you should get the results you want.
    Code:
    Sub Copyandsort()
    
        Columns("D:D").Select
        Selection.Copy
        Sheets("Sheet4").Select
        Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet1").Select
        Columns("A:A").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet4").Select
        Range("B1").Select
        ActiveSheet.Paste
        Sheets("Sheet1").Select
        Columns("C:C").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet4").Select
        Range("C1").Select
        ActiveSheet.Paste
        Columns("A:A").Select
        Selection.Replace What:="01", Replacement:="1", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Selection.Replace What:="02", Replacement:="2", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Selection.Replace What:="03", Replacement:="3", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Selection.Replace What:="04", Replacement:="4", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Selection.Replace What:="05", Replacement:="5", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Selection.Replace What:="06", Replacement:="6", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Selection.Replace What:="07", Replacement:="7", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Selection.Replace What:="08", Replacement:="8", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Selection.Replace What:="09", Replacement:="9", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    End Sub
    
    In this, I had the original data in columns ABC on Sheet1, then entered the formula I gave above into column D and filled the column down. This was then the first data copied to Sheet4.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/933917

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice