sorting numbers
 

i have a simple sheet that basically consists of

name, ip address, and then other columns

obviously when i sort by ip address the .1,.2,.3,.4,.5, etc get sorted
in between the higher numbers ie:

.99
.100
.1
.101
.102

and i have to manually move them

i cant seem to see how you can sort within a column

if the above makes sense :)


--
adetorry
------------------------------------------------------------------------
adetorry's Profile: http://www.excelforum.com/member.php...o&userid=29151
View this thread: http://www.excelforum.com/showthread...hreadid=488723

sorting numbers
 

See:

http://www.microsoft.com/office/comm...cb6&sloc=en-us
--
Gary''s Student


"adetorry" wrote:


i have a simple sheet that basically consists of

name, ip address, and then other columns

obviously when i sort by ip address the .1,.2,.3,.4,.5, etc get sorted
in between the higher numbers ie:

.99
.100
.1
.101
.102

and i have to manually move them

i cant seem to see how you can sort within a column

if the above makes sense :)


--
adetorry
------------------------------------------------------------------------
adetorry's Profile: http://www.excelforum.com/member.php...o&userid=29151
View this thread: http://www.excelforum.com/showthread...hreadid=488723

sorting numbers
 

Hi,

Assuming that the IP addresses are in column B starting at B2, enter the
following, somewhat loooong, formula in C2 (or Row 2 of any blank column)
and drag the formula down the rest of the column.

=LEFT(B2,FIND(".",B2)-1)*1000000000+MID(B2,FIND("x",SUBSTITUTE(B2,".","x ",1))+1,FIND("x",SUBSTITUTE(B2,".","x",2))-FIND(".",B2)-1)*1000000+MID(B2,FIND("x",SUBSTITUTE(B2,".","x",2 ))+1,FIND("x",SUBSTITUTE(B2,".","x",3))-FIND("x",SUBSTITUTE(B2,".","x",2))-1)*1000+RIGHT(B2,LEN(B2)-FIND("x",SUBSTITUTE(B2,".","x",3)))

Format the helper column as 'Number' with zero digits after the decimal
(This step is only for cosmetic purposes and is therefore optional).

Sort the sheet by the helper column.

Note: The formula assumes that there are no more than three digits in the
second and subsequent parts of any IP address, which I believe is true for
all IP addresses currently (in fact, many contain two-digit parts). The
formula can be modified to accommodate more digits if need arises.

Regards,
B. R. Ramachandran


"adetorry" wrote:


i have a simple sheet that basically consists of

name, ip address, and then other columns

obviously when i sort by ip address the .1,.2,.3,.4,.5, etc get sorted
in between the higher numbers ie:

.99
.100
.1
.101
.102

and i have to manually move them

i cant seem to see how you can sort within a column

if the above makes sense :)


--
adetorry
------------------------------------------------------------------------
adetorry's Profile: http://www.excelforum.com/member.php...o&userid=29151
View this thread: http://www.excelforum.com/showthread...hreadid=488723

sorting numbers
 

My Excel add-in Special Sort can sort on the numbers in a text entry.
(there are 4 different ways to get a true numeric sort order)
It has over 20 different sort methods not readily available in Excel.

They include sorting by...
color, prefix, middle, suffix, random, reverse,
no articles, dates, decimal (ip and dewey), length and others.
Looks and works somewhat like the regular Excel sort utility.
It comes with a Word.doc install/use file.

It is - free - just email me and ask for it. (release 1.51)
Remove XXX from my email address.

Jim Cone
San Francisco, USA
XX

sorting numbers
 

On Mon, 28 Nov 2005 08:21:51 -0600, adetorry
wrote:


i have a simple sheet that basically consists of

name, ip address, and then other columns

obviously when i sort by ip address the .1,.2,.3,.4,.5, etc get sorted
in between the higher numbers ie:

99
100
1
101
102

and i have to manually move them

i cant seem to see how you can sort within a column

if the above makes sense :)

Try this VBA Sub.

To use it, select either one cell in the table, in which case the entire table
will be sorted; or select the area you wish to sort, in which case just the
selected cells will be sorted. The <alt-F8 opens the macro dialog box.
Select SortIP and RUN.

At least one column must be IP addresses in the usual form.

The Sub tests for a column header -- none of the headers can "look like" an IP
address.

To enter the Sub, <alt-F11 opens the VB Editor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.

======================================
Option Explicit
Sub sortIP() 'sorts IP addresses
Dim i As Long, j As Long, k As Long
Dim IP
Dim rg()
Dim RangeToSort As Range
Dim IPaddress As String
Dim IPColumn As Long

IPaddress = "#*.#*.#*.#*"

Set RangeToSort = Selection

'If just one cell selected, then expand to current region
If RangeToSort.Count = 1 Then
Set RangeToSort = RangeToSort.CurrentRegion
End If

'Check if row 1 contains an IP address. If not, it is a header row

'first find column with IP addresses. Check row 2 since row 1 might be header
IPColumn = 1
Do Until RangeToSort.Cells(2, IPColumn).Text Like IPaddress
IPColumn = IPColumn + 1
If IPColumn RangeToSort.Columns.Count Then
MsgBox ("No valid IP address found in Row 1 or Row 2")
Exit Sub
End If
Loop

If Not RangeToSort(1, IPColumn).Text Like IPaddress Then
Set RangeToSort = RangeToSort.Offset(1, 0). _
Resize(RangeToSort.Rows.Count - 1, RangeToSort.Columns.Count)
End If



'one extra column for the IP sort order
ReDim rg(RangeToSort.Rows.Count - 1, RangeToSort.Columns.Count)



For i = 0 To UBound(rg)
For k = 1 To UBound(rg, 2)
rg(i, k) = RangeToSort.Cells(i + 1, k).Text
Next k
IP = Split(rg(i, IPColumn), ".")
For j = 0 To 3
rg(i, 0) = rg(i, 0) & Right("000" & IP(j), 3)
Next j

Next i

rg = BubbleSort(rg, 0)

For i = 0 To UBound(rg)
For k = 1 To UBound(rg, 2)
RangeToSort.Cells(i + 1, k) = rg(i, k)
Next k
Next i

End Sub

Function BubbleSort(TempArray As Variant, d As Long) 'D is dimension to sort on
Dim Temp() As Variant
Dim i As Integer, j As Integer, k As Integer
Dim NoExchanges As Boolean

k = UBound(TempArray, 2)
ReDim Temp(0, k)

Do
NoExchanges = True

For i = 0 To UBound(TempArray) - 1
If TempArray(i, d) TempArray(i + 1, d) Then
NoExchanges = False
For j = 0 To k
Temp(0, j) = TempArray(i, j)
TempArray(i, j) = TempArray(i + 1, j)
TempArray(i + 1, j) = Temp(0, j)
Next j
End If
Next i
Loop While Not NoExchanges

BubbleSort = TempArray

End Function
=================================


--ron

sorting numbers
 

OOps. Small bug in routine will prevent if from working if IP addresses are in
first column.

Use this instead:

========================================
Sub sortIP() 'sorts IP addresses
Dim i As Long, j As Long, k As Long
Dim IP
Dim rg()
Dim RangeToSort As Range
Dim IPaddress As String
Dim IPColumn As Long

IPaddress = "#*.#*.#*.#*"

Set RangeToSort = Selection

'If just one cell selected, then expand to current region
If RangeToSort.Count = 1 Then
Set RangeToSort = RangeToSort.CurrentRegion
End If

'Check if row 1 contains an IP address. If not, it is a header row

'first find column with IP addresses. Check row 2 since row 1 might be header
IPColumn = 1
Do Until RangeToSort.Cells(2, IPColumn).Text Like IPaddress
If IPColumn RangeToSort.Columns.Count Then
MsgBox ("No valid IP address found in Row 1 or Row 2")
Exit Sub
End If
IPColumn = IPColumn + 1
Loop

If Not RangeToSort(1, IPColumn).Text Like IPaddress Then
Set RangeToSort = RangeToSort.Offset(1, 0). _
Resize(RangeToSort.Rows.Count - 1, RangeToSort.Columns.Count)
End If



'one extra column for the IP sort order
ReDim rg(RangeToSort.Rows.Count - 1, RangeToSort.Columns.Count)



For i = 0 To UBound(rg)
For k = 1 To UBound(rg, 2)
rg(i, k) = RangeToSort.Cells(i + 1, k).Text
Next k
IP = Split(rg(i, IPColumn), ".")
For j = 0 To 3
rg(i, 0) = rg(i, 0) & Right("000" & IP(j), 3)
Next j

Next i

rg = BubbleSort(rg, 0)

For i = 0 To UBound(rg)
For k = 1 To UBound(rg, 2)
RangeToSort.Cells(i + 1, k) = rg(i, k)
Next k
Next i

End Sub

Function BubbleSort(TempArray As Variant, d As Long) 'D is dimension to sort on
Dim Temp() As Variant
Dim i As Integer, j As Integer, k As Integer
Dim NoExchanges As Boolean

k = UBound(TempArray, 2)
ReDim Temp(0, k)

Do
NoExchanges = True

For i = 0 To UBound(TempArray) - 1
If TempArray(i, d) TempArray(i + 1, d) Then
NoExchanges = False
For j = 0 To k
Temp(0, j) = TempArray(i, j)
TempArray(i, j) = TempArray(i + 1, j)
TempArray(i + 1, j) = Temp(0, j)
Next j
End If
Next i
Loop While Not NoExchanges

BubbleSort = TempArray

End Function
==================================

On Mon, 28 Nov 2005 20:30:59 -0500, Ron Rosenfeld
wrote:

On Mon, 28 Nov 2005 08:21:51 -0600, adetorry
wrote:


i have a simple sheet that basically consists of

name, ip address, and then other columns

obviously when i sort by ip address the .1,.2,.3,.4,.5, etc get sorted
in between the higher numbers ie:

99
100
1
101
102

and i have to manually move them

i cant seem to see how you can sort within a column

if the above makes sense :)

Try this VBA Sub.

To use it, select either one cell in the table, in which case the entire table
will be sorted; or select the area you wish to sort, in which case just the
selected cells will be sorted. The <alt-F8 opens the macro dialog box.
Select SortIP and RUN.

At least one column must be IP addresses in the usual form.

The Sub tests for a column header -- none of the headers can "look like" an IP
address.

To enter the Sub, <alt-F11 opens the VB Editor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.

======================================
Option Explicit
Sub sortIP() 'sorts IP addresses
Dim i As Long, j As Long, k As Long
Dim IP
Dim rg()
Dim RangeToSort As Range
Dim IPaddress As String
Dim IPColumn As Long

IPaddress = "#*.#*.#*.#*"

Set RangeToSort = Selection

'If just one cell selected, then expand to current region
If RangeToSort.Count = 1 Then
Set RangeToSort = RangeToSort.CurrentRegion
End If

'Check if row 1 contains an IP address. If not, it is a header row

'first find column with IP addresses. Check row 2 since row 1 might be header
IPColumn = 1
Do Until RangeToSort.Cells(2, IPColumn).Text Like IPaddress
IPColumn = IPColumn + 1
If IPColumn RangeToSort.Columns.Count Then
MsgBox ("No valid IP address found in Row 1 or Row 2")
Exit Sub
End If
Loop

If Not RangeToSort(1, IPColumn).Text Like IPaddress Then
Set RangeToSort = RangeToSort.Offset(1, 0). _
Resize(RangeToSort.Rows.Count - 1, RangeToSort.Columns.Count)
End If



'one extra column for the IP sort order
ReDim rg(RangeToSort.Rows.Count - 1, RangeToSort.Columns.Count)



For i = 0 To UBound(rg)
For k = 1 To UBound(rg, 2)
rg(i, k) = RangeToSort.Cells(i + 1, k).Text
Next k
IP = Split(rg(i, IPColumn), ".")
For j = 0 To 3
rg(i, 0) = rg(i, 0) & Right("000" & IP(j), 3)
Next j

Next i

rg = BubbleSort(rg, 0)

For i = 0 To UBound(rg)
For k = 1 To UBound(rg, 2)
RangeToSort.Cells(i + 1, k) = rg(i, k)
Next k
Next i

End Sub

Function BubbleSort(TempArray As Variant, d As Long) 'D is dimension to sort on
Dim Temp() As Variant
Dim i As Integer, j As Integer, k As Integer
Dim NoExchanges As Boolean

k = UBound(TempArray, 2)
ReDim Temp(0, k)

Do
NoExchanges = True

For i = 0 To UBound(TempArray) - 1
If TempArray(i, d) TempArray(i + 1, d) Then
NoExchanges = False
For j = 0 To k
Temp(0, j) = TempArray(i, j)
TempArray(i, j) = TempArray(i + 1, j)
TempArray(i + 1, j) = Temp(0, j)
Next j
End If
Next i
Loop While Not NoExchanges

BubbleSort = TempArray

End Function
=================================


--ron

--ron