- 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.
Appreciated any help!
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!