+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    Registered User
    Join Date
    11-10-2010
    Location
    Boston, USA
    MS-Off Ver
    Excel 2003
    Posts
    28

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

    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)

  2. #2
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,121

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

    We'll need a sample file. Be sure to include enough data to cover your requirements.
    David
    (*) Reputation points appreciated.

  3. #3
    Registered User
    Join Date
    11-10-2010
    Location
    Boston, USA
    MS-Off Ver
    Excel 2003
    Posts
    28

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

    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)
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

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

    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

  5. #5
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

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

    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
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    11-10-2010
    Location
    Boston, USA
    MS-Off Ver
    Excel 2003
    Posts
    28

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

    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!

  7. #7
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

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

    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
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    11-10-2010
    Location
    Boston, USA
    MS-Off Ver
    Excel 2003
    Posts
    28

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

    Hi Alastair - works GREAT!

    THANKS for all your help!

  9. #9
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

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

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Limit copy range baised on visible data
    By JO505 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-22-2013, 08:48 PM
  2. Sorting, finding dulicates, moving one data element up, deleting original data
    By rickwtx in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 01-13-2011, 07:32 PM
  3. Moving rows, counting, and deleting original row
    By kollinsb in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-11-2010, 12:04 AM
  4. Moving and Deleting Data from Sheet1 to other sheets in same Workbook
    By RMay in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-27-2009, 01:13 PM
  5. Deleting worksheets, printing to Adobe, and moving to a new workbook
    By baconcow in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-22-2008, 10:03 AM

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