Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting numbers, please help? | Excel Discussion (Misc queries) | |||
Sorting numbers | New Users to Excel | |||
Match Last Occurrence of two numbers and Return Date | Excel Worksheet Functions | |||
Match Last Occurrence of two numbers and Count to Previous Occurence | Excel Worksheet Functions | |||
Sorting when some numbers have a text suffix | Excel Discussion (Misc queries) |