Sorting problem

[JC]

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.

 

[Myrna Larson]

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 -

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 -

 

[JC]

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?

 

[Guest]

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