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

• ### 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

• 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 Wednesday, September 23, 2015 7:08 PM
• Proposed as answer by Wednesday, September 23, 2015 7:43 PM
• Marked as answer by 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 Wednesday, September 23, 2015 7:08 PM
• Proposed as answer by Wednesday, September 23, 2015 7:43 PM
• Marked as answer by 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
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A:E")