sorting IP addresses


G

Guest

I have a range of IP Addresses (xx.xx.xxx.1 through .255). When I sort by
this column, I get xx.xx.xxx.0 through .99 followed by xx.xx.xxx.1 through
..199 and then 2 - 255. Is there a way to force it so that it sorts in a
sequencial 1,2,3,4... order?
 
Ad

Advertisements

J

Jim Cone

PTW,
My Excel add-in Special Sort will do that along with more than 20 other
sorts not readily available in Excel.
Looks and works something like the built in sort utility.
Comes with a Word.doc install/use file.
It is available for - free - email me and ask for it.
Remove XXX from my email address.
Jim Cone
San Francisco, USA
(e-mail address removed)


"PattiTechWriter" <[email protected]ons.microsoft.com>
wrote in message
I have a range of IP Addresses (xx.xx.xxx.1 through .255). When I sort by
this column, I get xx.xx.xxx.0 through .99 followed by xx.xx.xxx.1 through
..199 and then 2 - 255. Is there a way to force it so that it sorts in a
sequencial 1,2,3,4... order?
 
Ad

Advertisements

R

Ron Rosenfeld

I have a range of IP Addresses (xx.xx.xxx.1 through .255). When I sort by
this column, I get xx.xx.xxx.0 through .99 followed by xx.xx.xxx.1 through
.199 and then 2 - 255. Is there a way to force it so that it sorts in a
sequencial 1,2,3,4... order?
Here is a VBA Macro that will sort IP addresses.

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

To use this, either select a range of cells, or select a single cell in the
area of the IP addresses. Then <alt-F8> opens the macro dialog box. Select
SortIP and RUN.

==========================================
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 a 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
=========================================
--ron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top