Sorting numbers with multiple decimal points?

  • Thread starter Thread starter Jonathan
  • Start date Start date
J

Jonathan

Hi - I'm sure this is covered in some docs somewhere, but it's hard to
know what to search for.

I have a spreadsheet (using Excel 2003 SP1) in which I have a number of
rows that have an "ID" field taking the format "X.X.X.X" where X is a
number from 0 to 100. Like this:

1.1.0.0 blah blah rest of row
1.1.2.4 blah blah rest of row
1.1.3.0 blah blah rest of row
1.0.0.0 blah blah rest of row
1.1.3.2 blah blah rest of row
1.1.3.1 blah blah rest of row
1.1.1.1 blah blah rest of row
1.1.3.0 blah blah rest of row
1.1.1.3 blah blah rest of row
1.1.2.5 blah blah rest of row
1.1.1.2 blah blah rest of row
.... Etc.

How can I sort these rows so they look like this (i.e. in numerical
order by ID number):

1.0.0.0 blah blah rest of row
1.1.0.0 blah blah rest of row
1.1.1.1 blah blah rest of row
1.1.1.2 blah blah rest of row
1.1.1.3 blah blah rest of row
1.1.2.4 blah blah rest of row
1.1.2.5 blah blah rest of row
1.1.3.0 blah blah rest of row
1.1.3.1 blah blah rest of row
1.1.3.2 blah blah rest of row

Thanks for any tips!

Jonathan
 
For the numbers you show, they sort that way now. The only place where I
would see a problem is when you get to numbers like

1.1.10.1

In that case, you need to copy the column to the far right, then on that
copy of the data do Text to Columns; select delimited, then select period
as the delimiter. Now sort the last 3 columns (the far right columns of the
new columns), then sort again on the first column of the new column (you
have to sort a max of 3 columns at a time. Excel's sort is
stable/persistent, so you can progress from least important to most
important using multiple sorts to sort on more than 3 columns).
 
Tom said:
For the numbers you show, they sort that way now. The only place where I
would see a problem is when you get to numbers like

1.1.10.1

In that case, you need to copy the column to the far right, then on that
copy of the data do Text to Columns; select delimited, then select period
as the delimiter. Now sort the last 3 columns (the far right columns of the
new columns), then sort again on the first column of the new column (you
have to sort a max of 3 columns at a time. Excel's sort is
stable/persistent, so you can progress from least important to most
important using multiple sorts to sort on more than 3 columns).
Click to expand...

Thanks - I'll give that a try. If not I think I can use GNU sort with
cut, but it'll mean exporting/importing though.
 
Hi - I'm sure this is covered in some docs somewhere, but it's hard to
know what to search for.

I have a spreadsheet (using Excel 2003 SP1) in which I have a number of
rows that have an "ID" field taking the format "X.X.X.X" where X is a
number from 0 to 100. Like this:

1.1.0.0 blah blah rest of row
1.1.2.4 blah blah rest of row
1.1.3.0 blah blah rest of row
1.0.0.0 blah blah rest of row
1.1.3.2 blah blah rest of row
1.1.3.1 blah blah rest of row
1.1.1.1 blah blah rest of row
1.1.3.0 blah blah rest of row
1.1.1.3 blah blah rest of row
1.1.2.5 blah blah rest of row
1.1.1.2 blah blah rest of row
... Etc.
Click to expand...

Are these IP addresses?
--ron
 
No - they're ID numbers.
Click to expand...

I would add an adjacent helper column that is the ID column converted to "real"
numbers; then sort on that and delete the helper column.

Probably best done using a macro, although you could devise a worksheet formula
that would do the conversion.

Something like this macro might give you some ideas. You might have to make
some changes depending on your worksheet layout. The macro assumes the columns
are sorted; and that the range with ID numbers is Named "ID".

============================
Sub SortID()
Range("ID").Insert xlShiftToRight
MakeNum Range("ID")

Range("ID").CurrentRegion.Sort Key1:=Range("ID").Offset(0, -1), _
Order1:=xlAscending, Header:=xlYes, _
MatchCase:=False, Orientation:=xlTopToBottom

Range("ID").Offset(0, -1).Delete xlShiftToLeft

End Sub
'--------------------------------------------
Sub MakeNum(rg As Range)
Dim i As Long
Dim c As Range
Dim N
Dim num

For Each c In rg
num = ""
N = Split(c.Text, ".")

If UBound(N) = 3 Then
For i = 0 To 3
num = num & Right("0" & N(i), 2)
Next i
Else: num = N
End If

c.Offset(0, -1).Value = num
Next c

End Sub
==============================


--ron
 
Ron said:
I would add an adjacent helper column that is the ID column converted to "real"
numbers; then sort on that and delete the helper column.

Probably best done using a macro, although you could devise a worksheet formula
that would do the conversion.

Something like this macro might give you some ideas. You might have to make
some changes depending on your worksheet layout. The macro assumes the columns
are sorted; and that the range with ID numbers is Named "ID".

============================
Sub SortID()
Range("ID").Insert xlShiftToRight
MakeNum Range("ID")

Range("ID").CurrentRegion.Sort Key1:=Range("ID").Offset(0, -1), _
Order1:=xlAscending, Header:=xlYes, _
MatchCase:=False, Orientation:=xlTopToBottom

Range("ID").Offset(0, -1).Delete xlShiftToLeft

End Sub
'--------------------------------------------
Sub MakeNum(rg As Range)
Dim i As Long
Dim c As Range
Dim N
Dim num

For Each c In rg
num = ""
N = Split(c.Text, ".")

If UBound(N) = 3 Then
For i = 0 To 3
num = num & Right("0" & N(i), 2)
Next i
Else: num = N
End If

c.Offset(0, -1).Value = num
Next c

End Sub
==============================
Click to expand...


Hey thanks Ron, that looks just what I'm looking for!
 
Hey thanks Ron, that looks just what I'm looking for!
Click to expand...

Jonathan,

mea culpa.

When I was writing the macro, I was misremembering what you had wrote.

The macro as written will only handle 0<=X<=99.

If X can equal 100, you need to make a small change:
For i = 0 To 3
Click to expand...
--------->> num = num & Right("00" & N(i), 3)
Next i
Click to expand...


--ron
 
Ron said:
Jonathan,

mea culpa.

When I was writing the macro, I was misremembering what you had wrote.

The macro as written will only handle 0<=X<=99.

If X can equal 100, you need to make a small change:



--------->> num = num & Right("00" & N(i), 3)
Click to expand...


Ah - good, got it, thanks again!
 
Back
Top