Sorting numbers with multiple decimal points?

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

You can directly sort it goign to Data > Sort

Mangesh


"Jonathan" <sorry@this_is_fake.com> wrote in message
news:[email protected]...
> 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).

--
Regards,
Tom Ogilvy

"Jonathan" <sorry@this_is_fake.com> wrote in message
news:[email protected]...
> 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

Tom Ogilvy wrote:
> 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).
>

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.

On Fri, 13 May 2005 12:20:10 +0100, Jonathan <sorry@this_is_fake.com> wrote:

>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.
>

Are these IP addresses?
--ron

Ron Rosenfeld wrote:
> On Fri, 13 May 2005 12:20:10 +0100, Jonathan <sorry@this_is_fake.com> wrote:
>
>
>>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.
>>
>
>
> Are these IP addresses?
> --ron

No - they're ID numbers.

On Mon, 16 May 2005 11:10:32 +0100, Jonathan <sorry@this_is_fake.com> wrote:


>
>No - they're ID numbers.

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 Rosenfeld wrote:
> On Mon, 16 May 2005 11:10:32 +0100, Jonathan <sorry@this_is_fake.com> wrote:
>
>
>
>>No - they're ID numbers.
>
>
> 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
> ==============================
>
>


Hey thanks Ron, that looks just what I'm looking for!

On Tue, 17 May 2005 00:29:04 +0100, Jonathan <sorry@this_is_fake.com> wrote:

>Hey thanks Ron, that looks just what I'm looking for!

You're welcome. I hope it works out for you.

--ron

On Tue, 17 May 2005 00:29:04 +0100, Jonathan <sorry@this_is_fake.com> wrote:

>Hey thanks Ron, that looks just what I'm looking for!

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
--------->> num = num & Right("00" & N(i), 3)
> Next i


--ron

Ron Rosenfeld wrote:
> On Tue, 17 May 2005 00:29:04 +0100, Jonathan <sorry@this_is_fake.com> wrote:
>
>
>>Hey thanks Ron, that looks just what I'm looking for!
>
>
> 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
>
> --------->> num = num & Right("00" & N(i), 3)
>
>> Next i


Ah - good, got it, thanks again!