ansaurus

Question

Answer 1

+3 A:

Split the column (using Text to Columns) so that you sort by the letters first, then the numeric column. Then recombine your columns.

JYelton 2010-06-17 17:05:53

thanks but i dont believe it is possible to split the column so that alpha and numeric are separated

I__ 2010-06-17 17:11:36

As long as all your data is like the sample (one character alpha) just split the column by `fixed length`. One character to the first column, all remaining characters to the second column.

JYelton 2010-06-17 17:40:24

yes that is a good point indeed

I__ 2010-06-17 17:43:55

Answer 2

+1 A:

here's what i did. i first did a little string manipulation. instead of A03, i put 03A in the cell. then i sorted the entire thing. then i put it back to A03

Dim replace_string As String
Dim replace_number As String

For i = 20 To 98
    replace_string = Mid(Cells(i, 4), 1, 1)
    replace_number = Mid(Cells(i, 4), 2, 2)
    Cells(i, 4) = replace_number + replace_string
Next i


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

For i = 20 To 98
    replace_string = Mid(Cells(i, 4), 3, 1)
    replace_number = Mid(Cells(i, 4), 1, 2)
    Cells(i, 4) = replace_string + replace_number
Next i

I__ 2010-06-17 17:25:49

ansaurus

tags:

views:

answers:

unconventional sorting in excel

related questions