sorting numbers

  • Thread starter Thread starter adetorry
  • Start date Start date
A

adetorry

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 :)
 
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
 
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
(e-mail address removed)
 
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 :)
Click to expand...

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

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
Click to expand...

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

Similar Threads

linking within a workbook and sorting 2
Sorting Numbers that have Text 2
Sorting Numbers 3
Sorting Madness 5
Sorting Numbers 2
ecxel sort 1
how to sort number properly in excel? 5
Sorting data bust ignoring numbers 3

Back
Top