Questions

MS Excel 2003 Filtering Assistance Needed

+
0 Votes
Locked

MS Excel 2003 Filtering Assistance Needed

BrabanderA
Greetings,

I am trying to filter and condense a large list of IP Addresses representing 5 days worth of OWA Access Logs. I've paired out all duplicate addresses already, but there are still thousands of unique address entries per day, and I am stumped as to how to condense this information (using Excel) to generate results similar to the following:

Original List:
10.13.XXX.XXX
10.13.XXX.XXX

10.17.XXX.XXX
10.17.XXX.XXX
10.17.XXX.XXX

10.5.XXX.XXX

13.10.XXX.XXX
13.10.XXX.XXX
13.10.XXX.XXX
13.10.XXX.XXX

120.104.XXX.XXX
120.104.XXX.XXX
120.104.XXX.XXX

Desired Filtered/Condensed List:

Column A Column B
10.13.XXX.XXX 1
10.17.XXX.XXX 3
10.5.XXX.XXX 1
13.10.XXX.XXX 4
120.104.XXX.XXX 3

I am simply trying to determine how many visits total on any given day originated from various subnets, but I am not having any luck getting Excel to generate the output I am looking for. Any help would be very much appreciated.

Signed,
Adam "Excel'Clueless"
  • +
    0 Votes
    Triathlete1981

    someone manually types in these IPs into an Excel sheet. i don't know what you mean by OWA, but i know some excel.

    if one column is the list of IPs and the adjacent column is the date the IP was used, if you highlight the two column headings (date and IP), go to Data, Filter, and Autofilter. you'll see a scroll down arrow box in each heading. click the arrow and you'll see anything entered in the column below the heading. choose the date you want and bam, the IP column will filter along with the date you chose and you'll organize by dates.

    or, if that doesn't work, you can highlight the data, click the Data menu and choose Sort. then have at it.

    +
    0 Votes
    mike.modiano

    Looks like you want a count of each unique item in the list of IP addresses. Highlight the entire list (or name it as a range) go to Data>Pivot Table, click Next select the range or range name (the highlighted range should already be in the box), deside where in the file to put the pivot table. Since this is only a one column list, drop the column name in thr row feild, and in the column feild; default field type is count.
    You are done:


    Count of Original List:
    Original List: Total
    10.13.XXX.XXX 2
    10.17.XXX.XXX 3
    10.5.XXX.XXX 1
    120.104.XXX.XXX 3
    13.10.XXX.XXX 4
    Grand Total 13

    Pivot Table output from the list provided.

  • +
    0 Votes
    Triathlete1981

    someone manually types in these IPs into an Excel sheet. i don't know what you mean by OWA, but i know some excel.

    if one column is the list of IPs and the adjacent column is the date the IP was used, if you highlight the two column headings (date and IP), go to Data, Filter, and Autofilter. you'll see a scroll down arrow box in each heading. click the arrow and you'll see anything entered in the column below the heading. choose the date you want and bam, the IP column will filter along with the date you chose and you'll organize by dates.

    or, if that doesn't work, you can highlight the data, click the Data menu and choose Sort. then have at it.

    +
    0 Votes
    mike.modiano

    Looks like you want a count of each unique item in the list of IP addresses. Highlight the entire list (or name it as a range) go to Data>Pivot Table, click Next select the range or range name (the highlighted range should already be in the box), deside where in the file to put the pivot table. Since this is only a one column list, drop the column name in thr row feild, and in the column feild; default field type is count.
    You are done:


    Count of Original List:
    Original List: Total
    10.13.XXX.XXX 2
    10.17.XXX.XXX 3
    10.5.XXX.XXX 1
    120.104.XXX.XXX 3
    13.10.XXX.XXX 4
    Grand Total 13

    Pivot Table output from the list provided.