Sorting IP Address

[Guest]

How do I sort fields that contain IP numbers in order? ex. Field A -
192.168.1.0.......192.168.1.121.....192.168.1.15....192.168.2.12....192.168.2.20...192.168.3.50

How can I sort these by the 3rd and 4th octives?

 

[Guest]

Hi,

Pardon me for my ignorance! Are the 4 parts in an IP address called
'octives'?

In principle, you could use a combination of LEFT, RIGHT, and LEN functions
to isolate the 3rd and 4th octives into separate columns and then sort them
by those two columns.

But, I thought that the following approach could be simpler.

Let us assume that Column A contains the IP numbers. If there are data in
other columns (Column B, .....) as well, insert 4 blank columns after A.
Copy Column A and paste it in Column B. Select Column B --> "Data" --> "Text
to Columns" --> Select "Delimited" --> "Next" --> Select "Other" and enter a
'period' sign in the box --> "Finish".
Now, the 3rd and 4th octives will be in columns D and E. After sorting the
entire page by columns D and E, you can get rid of columns B, C, D, and E.

Please note that all you need is four contiguous blank columns to do the
above, and they can be anywhere in the sheet. However I suggested that you
have them adjacent to Column A just for convenience.

Regards,
B. R. Ramachandran

 

[joeu2004]

Pardon me for my ignorance! Are the 4 parts in an
IP address called 'octives'?

No. They are called octets.

 

[Ron Rosenfeld]

How do I sort fields that contain IP numbers in order? ex. Field A -
192.168.1.0.......192.168.1.121.....192.168.1.15....192.168.2.12....192.168.2.20...192.168.3.50

How can I sort these by the 3rd and 4th octives?

Here is a VBA macro that should sort IP addresses.

It requires that the IP addresses be in a column (but it does not check for
that.

To enter this, <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.

To use the code, first select the column of IP addresses you wish to sort.
Then <alt><F8> opens the Macro dialog box. Select SortIP and RUN.

Backup your data first, just in case.

=========================
Sub sortIP() 'sorts IP addresses
Dim i As Long, j As Long
Dim IP
Dim rg()

ReDim rg(Selection.Count - 1, 1)

For i = 0 To UBound(rg)
rg(i, 0) = Selection.Cells(i + 1, 1).Text
IP = Split(rg(i, 0), ".")
For j = 0 To 3
rg(i, 1) = rg(i, 1) & Right("0" & Hex(IP(j)), 2)
Next j
rg(i, 1) = hex2dec(rg(i, 1))
Next i

rg = BubbleSort(rg, 1)

For i = 0 To UBound(rg)
Selection.Cells(i + 1, 1) = rg(i, 0)
Next i

End Sub
======================
--ron

 

[Ron Rosenfeld]

How do I sort fields that contain IP numbers in order? ex. Field A -
192.168.1.0.......192.168.1.121.....192.168.1.15....192.168.2.12....192.168.2.20...192.168.3.50

How can I sort these by the 3rd and 4th octives?

My previous post left out some parts of the macro. This one should be
complete:

==================================
Option Explicit

Sub sortIP() 'sorts IP addresses
Dim i As Long, j As Long
Dim IP
Dim rg()

ReDim rg(Selection.Count - 1, 1)

For i = 0 To UBound(rg)
rg(i, 0) = Selection.Cells(i + 1, 1).Text
IP = Split(rg(i, 0), ".")
For j = 0 To 3
rg(i, 1) = rg(i, 1) & Right("0" & Hex(IP(j)), 2)
Next j
rg(i, 1) = "&H" & (rg(i, 1))
Next i

rg = BubbleSort(rg, 1)

For i = 0 To UBound(rg)
Selection.Cells(i + 1, 1) = rg(i, 0)
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 Rosenfeld]

How do I sort fields that contain IP numbers in order? ex. Field A -
192.168.1.0.......192.168.1.121.....192.168.1.15....192.168.2.12....192.168.2.20...192.168.3.50

How can I sort these by the 3rd and 4th octives?

Slightly simpler:

==============================
==================================
Option Explicit

Sub sortIP() 'sorts IP addresses
Dim i As Long, j As Long
Dim IP
Dim rg()

ReDim rg(Selection.Count - 1, 1)

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

rg = BubbleSort(rg, 1)

For i = 0 To UBound(rg)
Selection.Cells(i + 1, 1) = rg(i, 0)
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

 

[Guest]

Thanks for the help. I am having a problem with an error when I run it.

Runtine error '9'

Subscript out of Range

I think the problem is the last line of the For loop prior to Next j
---------------------------------------------------------------------------

 

[Ron Rosenfeld]

Do you have an:

Option Base 1

statement that might be affecting things? (Option Base 0 is the default)

If so, and if you can't remove it, or set Option Base 0 for this module, you'll
have to explicitly declare the lower bounds of all arrays.

============================

In the first SUB, about Line 5
Change:
ReDim rg(Selection.Count - 1, 1)
To:
ReDim rg(0 To Selection.Count - 1, 0 To 1)

In the second SUB, about Line 6
Change:
ReDim Temp(0, k)
To:
ReDim Temp(0 To 0, 0 To k)

========================

Thanks for the help. I am having a problem with an error when I run it.

Runtine error '9'

Subscript out of Range

I think the problem is the last line of the For loop prior to Next j

--ron

 

[Guest]

Still getting error:


Sub sortIP() 'sorts IP addresses
Dim i As Long, j As Long
Dim IP
Dim rg()

ReDim rg(0 To Selection.Count - 1, 0 To 1)

For i = 0 To UBound(rg)
rg(i, 0) = Selection.Cells(i + 1, 1).Text
IP = Split(rg(i, 0), ".")
For j = 0 To 3
**>This where the macro stops<** rg(i, 1) = rg(i, 1) &
Right("000" & IP(j), 3)
Next j
Next i

rg = BubbleSort(rg, 1)

For i = 0 To UBound(rg)
Selection.Cells(i + 1, 1) = rg(i, 0)
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 To 0, 0 To 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 Rosenfeld]

1. I assume the line where the macro stops is all on one line and not split as
you show it. i.e.

rg(i, 1) = rg(i, 1) & Right("000" & IP(j), 3)

2. An invalid IP address (i.e. one without 3 '.''s will cause that error. Is
that a possibility?

For debugging purposes, modify the macro as follows:

....
Replace:

For j = 0 To 3
rg(i, 1) = rg(i, 1) & Right("000" & IP(j), 3)
Next j

-------------------
with:

If UBound(IP) <> 3 Then
MsgBox ("Invalid IP address of " & rg(i, 0) & " at " _
& Selection.Cells(i + 1, 1).Address)
Else
For j = 0 To 3
rg(i, 1) = rg(i, 1) & Right("000" & IP(j), 3)
Next j
End If

-------------------------------

and let's see what happens.

Still getting error:


Sub sortIP() 'sorts IP addresses
Dim i As Long, j As Long
Dim IP
Dim rg()

ReDim rg(0 To Selection.Count - 1, 0 To 1)

For i = 0 To UBound(rg)
rg(i, 0) = Selection.Cells(i + 1, 1).Text
IP = Split(rg(i, 0), ".")
For j = 0 To 3
**>This where the macro stops<** rg(i, 1) = rg(i, 1) &
Right("000" & IP(j), 3)
Next j
Next i

rg = BubbleSort(rg, 1)

For i = 0 To UBound(rg)
Selection.Cells(i + 1, 1) = rg(i, 0)
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 To 0, 0 To 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

 

[Guest]

neither works and i dont think if they did it would help my situation

i have list of ip addresses with asscoiated names & tel numbers

if i sort using just one column (ip addys) then the names and tel numbers
with then not be sorted as well ?

ade

 

[Ron Rosenfeld]

There's been nothing in this thread, that I can find, for the past month or so.
Nor do I find any old messages with your handle.

So I don't know what you mean when you say "neither works".

Perhaps if you restate your problem in its entirety, we can help.

neither works and i dont think if they did it would help my situation

i have list of ip addresses with asscoiated names & tel numbers

if i sort using just one column (ip addys) then the names and tel numbers
with then not be sorted as well ?

ade

--ron

 

[David McRitchie]

Looks like an old thread may have been reactivated, but for sorting IP numbers
take a look at
Sorting TCP/IP Addresses, and the like
http://www.mvps.org/dmcritchie/excel/sorttcp.htm