Moving Data to New Workbook Baised on Type - Then Counting IP's and deleting dup's

[mroldguy]

First off would like to say that everyone here is very helpful. You have given me a lot of help and for that I am truely greatful.

I wish I could do this my self but it seems as if I have lost one to many brain cells.



I have a report that I do compile every week. This report 6 columns

A - Date/Time
B - Error Type
C - IP Address
D - DNS Name
E - Error Message


There is approx 30 differant Error Type (column B).

What I was looking for is a way to:

1. Copy each Error Type to it's own worksheet

1A. Name this work sheet with Error Type Name

1B. Add a column F - count how many times an ip address has a cetian type of error type,

1C Delete row IF ip address count is above 1, (so if ip address 10.10.10.10 has a UNIX error tyoe 4 times column F will show 4 but only one row of data will be on workbook)

[Tinbendr]

We'll need a sample file. Be sure to include enough data to cover your requirements.

[mroldguy]

Hi Tinbendr - I have attached a spreadsheet.
First tab has all the errors on it . The other tabs are broken down by error type (column b) then I count how manyt times an IP address errored, put count in last column and delted other rows of data (please note that I onlyt care how many times an ip address has a certian error type)

[aydeegee]

Hi mroldguy

A couple of questions.
Your description and example do not agree. You say " count how many times an ip address has a cetian type of error type"

Your example shows:

Date Type IP Name Fault Occurrences
01/01/2014 AIX 10.1.2.3 Tree Bad Password 7

but I interpret your description as:

Date Type IP Name Fault Occurrences
01/01/2014 AIX 10.1.2.3 Tree Bad Password 2
02/01/2014 AIX 10.1.2.3 Tree Maintance 2
06/01/2014 AIX 10.1.2.3 Tree Network Issue 3

Please confirm which is correct.

Is this to be a one off thing, or will you want the data for one day and then need everything updated tomorrow, or will tomorrow's data be shown below the previous data?

Regards
Alastair

[aydeegee]

Hi mroldguy

I have supplied my own answers to my questions and assumed that (a) my interpretation is correct and (b) if new data is added, the summary will be overwritten.

Having written the macro, I realised that the results could be obtained using subtotals. (But that would put the results on the same page. Let me know if you are interested)

So, attached is the macro version. Click on the green Summary button to run the macro. To view the macro press Alt+F8 > step Into.

Let me know how you get on.

Regards
Alastair

[mroldguy]

Let me clarify this a little more. What I am looking to do is get everything that has same value in column B on its own sheet. Once it is on its own sheet tell me how many times the IP adress occurrs (and leave just one row with times Type occured...not concerned about the fault.

Example:
10.1.2.3 had 7 AIX errors and I would like the AIX sheet to look like
1/1/2014 AIX 10.1.2.3 Tree Bad Password 7

So in the data I supplied above the final result I was hoping to reach would be for AIX:

1/1/2014 AIX 10.1.2.3 Tree Bad Password 7
1/2/2014 AIX 10.1.2.4 BlueBird Maintance 1
1/5/2014 AIX 10.1.2.5 Sparrow Maintance 1
1/5/2014 AIX 10.1.2.6 Robin Maintance 1
1/2/2014 AIX 10.1.2.7 Bat Network Issue 1
1/2/2014 AIX 10.1.2.8 Frog User Error 1
1/2/2014 AIX 10.1.2.9 Pool Passed 6
1/8/2014 AIX 10.1.2.10 Hose User Error 1
1/9/2014 AIX 10.1.2.11 Fire Passed 1
1/1/2014 AIX 10.1.2.12 Volt Bad Password 8

Thanks for you help it is greatly appreicated!

[aydeegee]

Hi mroldguy

I think the attached should meet your requirements. You will note that I have removed the Fault from the summaries.

Let me know if you need any changes.

Regards
Alastair

[mroldguy]

Hi Alastair - works GREAT!

THANKS for all your help!

[aydeegee]

You're welcome - glad it works.

Perhaps you will mark this as solved? (Go to your first post and you can amend the title)


Regards
Alastair