unconventional sorting in excel
- by I__
i have a list like this:
G05
G03
F02
F06
G10
A03
A11
E10
E05
C11
C03
D03
A12
C12
F05
H03
C08
G02
D10
B12
C10
D11
C02
E11
E02
E03
H11
A08
D05
F04
A04
H07
D04
B07
F12
E04
B03
H05
C06
F08
C09
E08
G12
C04
B05
H09
A07
E09
C07
G07
G09
A06
D09
E07
E12
G04
A10
H02
G08
B06
B09
D06
F07
G06
A09
H06
D07
H04
H10
F10
B02
B10
F03
F11
D08
B11
B08
D12
H08
A05
i need it sorted in the following manner:
A03, B03, C03....A04, B04, C04.....A11, B11, C11........
the conventional sort can be done like this:
ActiveWorkbook.Worksheets("2871P1").Sort.SortFields.Add Key:=Range("D20:D99") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("2871P1").Sort
.SetRange Range("D20:E99")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
however with this method, we are going to get A01, A02, A03 etc..., but this is not what i need