Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sorting problem
Hi,
I am running WinXP and Office 2003 with all updates installed. I have my firewall email me the log each day which I then paste into Excel. The data is in the format Col. A Col. B Col. C Col. D Date & time Action taken Source IP address, port Destination IP address, port 2005/01/14 21:24:53.480 - UDP packet dropped - Source:w.x.y.z, port, WAN - Destination:w.x.y.z, port, WAN - where w, x, y and z are 1, 2 or 3 digit numbers. There are another 2 columns which are usually blank but sometimes have data. I am getting about 100 entries per day so the amount of data isn't large but is building - it was 60 per day a few months ago. I have been hitting <CTRL A to select all and then sorting on Col C so that I get all entries coming from an ISP grouped together. However, Excel has a funny way of sorting which seems to be based on each character moving from left to right. The result of this is that I get the following happening:- Source:211.177.154.134, 1030, WAN - Source:211.19.215.70, 1051, WAN - Source:211.19.215.70, 1677, WAN - Source:211.190.195.138, 2876, WAN - The problem with this is that the first and last come from one ISP and the middle 2 come from another which confuses the presentation. Is there a way I can get Excel to sort w, x, y and z as 3 digit numbers? I would like the result of the sort to be:- Source:211.19.215.70, 1051, WAN - Source:211.19.215.70, 1677, WAN - Source:211.177.154.134, 1030, WAN - Source:211.190.195.138, 2876, WAN - TIA. -- Cheers . . . JC |
#2
|
|||
|
|||
You can use Data/Text to columns to splt the numbers into 4 cells, then sort
on those 4 columns. The other alternative is to pad each sectionto 3 digits with 0, i.e. 211.019.215.070, 1051, WAN - On Fri, 28 Jan 2005 10:49:59 +1100, JC wrote: Hi, I am running WinXP and Office 2003 with all updates installed. I have my firewall email me the log each day which I then paste into Excel. The data is in the format Col. A Col. B Col. C Col. D Date & time Action taken Source IP address, port Destination IP address, port 2005/01/14 21:24:53.480 - UDP packet dropped - Source:w.x.y.z, port, WAN - Destination:w.x.y.z, port, WAN - where w, x, y and z are 1, 2 or 3 digit numbers. There are another 2 columns which are usually blank but sometimes have data. I am getting about 100 entries per day so the amount of data isn't large but is building - it was 60 per day a few months ago. I have been hitting <CTRL A to select all and then sorting on Col C so that I get all entries coming from an ISP grouped together. However, Excel has a funny way of sorting which seems to be based on each character moving from left to right. The result of this is that I get the following happening:- Source:211.177.154.134, 1030, WAN - Source:211.19.215.70, 1051, WAN - Source:211.19.215.70, 1677, WAN - Source:211.190.195.138, 2876, WAN - The problem with this is that the first and last come from one ISP and the middle 2 come from another which confuses the presentation. Is there a way I can get Excel to sort w, x, y and z as 3 digit numbers? I would like the result of the sort to be:- Source:211.19.215.70, 1051, WAN - Source:211.19.215.70, 1677, WAN - Source:211.177.154.134, 1030, WAN - Source:211.190.195.138, 2876, WAN - TIA. |
#3
|
|||
|
|||
I had thought of splitting the data across multiple columns but figured that I
would lose the association between the data in the 4 columns if I did that. Even if the data in the 4 columns could remain associated the numbers in each column would still need leading 0's inserted as required otherwise the sort result would be as it is now. I had also considered adding leading 0's. Unfortunately, I can't get the firewall to do this for me and I figured that manually editing 100+ records each day would become a bit too time consuming. Is there a way of automating this via a macro perhaps? I also considered creating a column comprising w*x*y*z and sorting on that column but realised that this would not create unique numbers since 200*100*150*2 would create the same number as 100*150*2*200 etc etc. The numbers would also be huge as each number is between 0 and 255. One possibility I thought of that would work, but have no clue how to achieve, would be to run a macro that converts each w.x.y.z number back to its 8 bit binary format - i.e. 01011011.00001111..... putting the result into say column G and then using Column G as the sort key. That would require that leading 0's were retained and not automatically deleted as is done with standard numbers. Any thoughts on adding the leading 0's or conversion to binary? On Thu, 27 Jan 2005 20:25:32 -0600, Myrna Larson wrote: You can use Data/Text to columns to splt the numbers into 4 cells, then sort on those 4 columns. The other alternative is to pad each sectionto 3 digits with 0, i.e. 211.019.215.070, 1051, WAN - On Fri, 28 Jan 2005 10:49:59 +1100, JC wrote: Hi, I am running WinXP and Office 2003 with all updates installed. I have my firewall email me the log each day which I then paste into Excel. The data is in the format Col. A Col. B Col. C Col. D Date & time Action taken Source IP address, port Destination IP address, port 2005/01/14 21:24:53.480 - UDP packet dropped - Source:w.x.y.z, port, WAN - Destination:w.x.y.z, port, WAN - where w, x, y and z are 1, 2 or 3 digit numbers. There are another 2 columns which are usually blank but sometimes have data. I am getting about 100 entries per day so the amount of data isn't large but is building - it was 60 per day a few months ago. I have been hitting <CTRL A to select all and then sorting on Col C so that I get all entries coming from an ISP grouped together. However, Excel has a funny way of sorting which seems to be based on each character moving from left to right. The result of this is that I get the following happening:- Source:211.177.154.134, 1030, WAN - Source:211.19.215.70, 1051, WAN - Source:211.19.215.70, 1677, WAN - Source:211.190.195.138, 2876, WAN - The problem with this is that the first and last come from one ISP and the middle 2 come from another which confuses the presentation. Is there a way I can get Excel to sort w, x, y and z as 3 digit numbers? I would like the result of the sort to be:- Source:211.19.215.70, 1051, WAN - Source:211.19.215.70, 1677, WAN - Source:211.177.154.134, 1030, WAN - Source:211.190.195.138, 2876, WAN - TIA. |
#4
|
|||
|
|||
Is there a way I can get Excel to sort w, x, y and z as 3 digit numbers? I would like the result of the sort to be:- Source:211.19.215.70, 1051, WAN - Source:211.19.215.70, 1677, WAN - Source:211.177.154.134, 1030, WAN - Source:211.190.195.138, 2876, WAN - Personally, I'd break up the IP addresses into 4 separate cells in a row and do sorts on those to get to where you want to be. Someone else here may have a better solution. For example, you find the "w" part of the address w.x.y.z in cell A1 by creating helper columns for values dot1, dot2 and dot3 then find w,x,y and z: dot1 = find(".",A1) dot2 = find(".",A1,dot1+1) dot3 = find(".",A1,dot2+1) w = left(A1,dot1-1) x = mid(A1,dot1+1,dot2-dot1-1) y = mid(A1,dot2+1,dot3-dot2-1) z = right(A1,len(A1)-dot3) Good luck... Bill |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem sorting in Excel | Excel Discussion (Misc queries) | |||
Difficult Sorting Problem | Excel Discussion (Misc queries) | |||
Crazy Sorting Problem..... | Excel Discussion (Misc queries) | |||
Sorting problem | Excel Discussion (Misc queries) | |||
Sorting problem | Excel Worksheet Functions |