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 :)
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
>
>
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
>
>
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
[email protected]XX
On Mon, 28 Nov 2005 08:21:51 -0600, adetorry
<[email protected]> 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
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 <[email protected]>
wrote:
>On Mon, 28 Nov 2005 08:21:51 -0600, adetorry
><[email protected]> 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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks