Excel Excel: Sort a column with #.#.# number and keep other column


Joined
Sep 22, 2015
Messages
1
Reaction score
0
Dear all,

How do I sort a column with "#.#.#" according to descending values in excel, while keeping the other columns 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

Required Output sort only second column:
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 the column.
Code:
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

Appreciated any help!
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top