# Excel - function to sort multiple columns of alphanumeric data

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

Not open for further replies.

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.

2. ### 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

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