none
Sort a column with #.#.# number and keep other column RRS feed

  • Question

  • Dear all,

    How do I sort a column with "#.#.#" according to descending values in excel, while keeping the other column on the same row?

    Number range is 0<= # >=100,000.

    Example for 2 columns:
    B 1.668.901
    A 8.515.492
    V 0.0.35
    K 1.579.319
    P 0.7.82
    C 4.263.57
    G 1.925.60
    H 0.0.0
    D 29589.292.235
    L 0.29.834
    J 1.870.904
    M 0.635.5
    S 0.0.355
    X 0.0.5

    Output sort only second column by descending order:
    D 29589.292.235
    A 8.515.492
    C 4.263.57
    G 1.925.60
    J 1.870.904
    B 1.668.901
    K 1.579.319
    M 0.635.5
    L 0.29.834
    P 0.7.82
    S 0.0.355
    V 0.0.35
    X 0.0.5
    H 0.0.0

    Code can sort by ascending but does not pull other column.

    Sub sortColumn()
        Dim arrData As Variant
        Dim i As Long, j As Long
        Dim temp As Variant
    'Range name is "ID"
        arrData = Range("ID").CurrentRegion.Value
       
        For i = 1 To UBound(arrData, 1)
            For j = i + 1 To UBound(arrData, 1)
                If getDesc(arrData(j, 1), arrData(i, 1)) Then
                    temp = arrData(i, 1)
                    arrData(i, 1) = arrData(j, 1)
                    arrData(j, 1) = temp
                End If
            Next j
        Next i
       
        Range("G1").Resize(UBound(arrData, 1), 2).Value = arrData
    End Sub
    
    Function getDesc(a As Variant, b As Variant)
        Dim aWords As Variant, bWords As Variant
        Dim i As Long
        aWords = Split(a & "..", ".")
        bWords = Split(b & "..", ".")
        For i = 0 To 2
            LT = Val(aWords(i)) < Val(bWords(i))
            If Val(aWords(i)) <> Val(bWords(i)) Then Exit For
        Next i
    End Function

    Thank you.

    Wednesday, September 23, 2015 3:14 PM

Answers

  • Select the data in column B.

    On the Data tab of the ribbon, click Text to Columns.

    Select Delimited, then click Next >.

    Enter a period (point, dot) in the 'Other' box, then click Next >.

    Specify C1 as target, then click Finish.

    Click any cell within the range.

    Still on the Data tab of the ribbon, click Sort.

    The entire range should be selected.

    Specify that you want to sort on column C, from largest to smallest.

    Click 'Add Level', then specify column D, from smallest to largest.

    Repeat for column E.

    Finally, click OK. Result:

    You can now delete columns C to E if you wish.

    You can record this as a macro.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    • Edited by Hans Vogelaar MVPMVP Wednesday, September 23, 2015 7:08 PM
    • Proposed as answer by André Santo Wednesday, September 23, 2015 7:43 PM
    • Marked as answer by heyweed Thursday, September 24, 2015 10:59 AM
    Wednesday, September 23, 2015 7:08 PM

All replies

  • Select the data in column B.

    On the Data tab of the ribbon, click Text to Columns.

    Select Delimited, then click Next >.

    Enter a period (point, dot) in the 'Other' box, then click Next >.

    Specify C1 as target, then click Finish.

    Click any cell within the range.

    Still on the Data tab of the ribbon, click Sort.

    The entire range should be selected.

    Specify that you want to sort on column C, from largest to smallest.

    Click 'Add Level', then specify column D, from smallest to largest.

    Repeat for column E.

    Finally, click OK. Result:

    You can now delete columns C to E if you wish.

    You can record this as a macro.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    • Edited by Hans Vogelaar MVPMVP Wednesday, September 23, 2015 7:08 PM
    • Proposed as answer by André Santo Wednesday, September 23, 2015 7:43 PM
    • Marked as answer by heyweed Thursday, September 24, 2015 10:59 AM
    Wednesday, September 23, 2015 7:08 PM
  • Thank you, sharing my code here:

    Sub sortColumn()
        Columns("B:B").Select
        Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
            :=".", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
            TrailingMinusNumbers:=True
        Range("A:E").Select
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C:C") _
            , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("D:D") _
            , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("E:E") _
            , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Sheet1").Sort
            .SetRange Range("A:E")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Columns("C:E").Select
        Range("E1").Activate
        Selection.Delete Shift:=xlToLeft
    End Sub

    Thursday, September 24, 2015 11:03 AM