+ Reply to Thread
Results 1 to 4 of 4

Sorting problem

  1. #1
    JC
    Guest

    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. #2
    Myrna Larson
    Guest

    Re: Sorting problem

    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 <[email protected]> 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. #3
    Bill Martin -- (Remove NOSPAM from address)
    Guest

    Re: Sorting problem


    > 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

  4. #4
    JC
    Guest

    Re: Sorting problem

    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
    <[email protected]> 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 <[email protected]> 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.



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1