Working with duplicates and multiple workbooks

**altafullahu** · 01-23-2013, 05:01 PM

Hey All!,

This is my first post, and after scouring the internet for a solution I can't seem to find exactly what I am looking for, just stuff that "might" work. I am looking for a more concrete solution, which is why I have finally decided to post!

OK so, let me try to explain the best I can.

Background of Objective

I work for a federal contractor. My current task is to go through audit logs of a server, these audit logs mark all successes and failures in regards to numerous stipulations and then stores them in a file (no file type, I had to force it to open in excel). I am in charge of 15 servers, each server has their own IP and their own log.

Some of these logs can go upwards of 2500 lines, it's a bit messy. My goal was (and I have completed this part) was to go through all the audit logs (Mind you, I have logs from October - now, I get logs on the first of every Monday) and find ONLY the failures, isolate them and move them into a separate spreadsheet (with the title syntax being "audit log failures 10-18-12") and organize them by the last few digits of their IP address on a per sheet basis.

So to summarize this the best way I can (I don't want to say what the data I am working with is, or the actual info, so every piece of data from here on out is fabricated), imagine I have 15 IPs.

1.2.3.4.5
1.2.3.4.6
1.2.3.4.7
1.2.3.4.8
..etc

I have given EACH server it's OWN sheet within it's respective workbook. So, I have a workbook titled "Audit Failures 10-18-2012", in that workbook are 15 sheets, each sheet has either a 2 or 3 digit name based on the last few digits of the IP address.

On top of this, not only am I reviewing the audit failures, but the security failures as well. They follow the exact same syntax and format of the audits the only difference being the data and the naming (the 2 pieces of data between audits and security are NOT shared and are NOT related, treat them as two separate things all together).

Problem

As I have oodles and oodles of data, I want to find a way to nicely prepare it so I can send it to my system admin for review. My goal is to isolate all the failures and only show the REPEATING failures from week to week and only the ones that are continuous.

For example:

Week 1: Failure A, Failure B, Failure C and Failure D
Week 2: Failure A, Failure C, Failure D
Week 3: Failure A, Failure B, Failure D
Week 3: Failure A, Failure D

As you can see, failures b and c were in week 1, but failure b wasn't in week 2 and failure c wasn't in week 3 but failures a and d remained throughout. That's in a nutshell what I am looking for, I am looking for outstanding failures over the month and which ones keep appearing and are not going away.

I understand the aforementioned is a bit tricky, which is why that's not the main focus, ideally all I really want to do is compare the spreadsheets of week 1, week 2, week 3 and week 4, highlight any and all duplicates ACROSS ALL THE WORKBOOKS and then go from there. Honestly, I don't even mind comparing two workbooks (though it will be longer) side by side to get the duplicates to show.

The main problem I am having is that I can't find a function that meets my unorthodox setup and criteria. I can't put all this data into one spreadsheet because it will give me almost 60 sheets and going through all that in one workbook would be a chore. It's also important to note (and this may make things easier) is that every piece of data is only in column A. No data stretches across multiple columns or rows.

TL:DR / In Summation All I am really looking for is a way to be able to look at 2 or more (pref more, but 2 is fine) spreadsheets side by side and be able to search for duplicates from sheet 1 Column A in the workbook of "Week 1" to sheet 1 Column A of the "Week 2" workbook. This will allow me to find the failures for the month of my audit logs and be able to compile the reoccurring failures into a neat list

I know this is a lot to ask for, and a lot to do. It's probably really simple and I have no problem trying my best to elaborate (of course without giving too much away). I could make a phony sheet if need be for you all to see, let me know if I need to do that.

Thanks a lot in advance!!

Usman

**BB1972** · 01-23-2013, 06:27 PM

Hi Usman, and welcome to the forum.

Regarding a "phony" sheet - yes, I would recommend that at the very least you upload a small, sample workbook, containing the type of data you're dealing with. Members can be disinclined to create your sample set (to work on, try formulas etc) as a precursor to trying to help you out.

Also, a sample workbook would give us an idea of how far you've come with this project - the forum is intended to assist people when they come up against specific problems, and not as a free development service.

If, therefore, you've taken a plan so far (even in your head) and have become stuck, please let us know what that sticking point might be.

Hope this helps.

**altafullahu** · 01-30-2013, 01:27 PM

Hey Brendan,

Sorry for the delay, been a busy past couple days!

I have created two spreadsheets titled:

Excel Test Sheet A
Excel Test Sheet B

On Spreadsheet A there are 3 sheets labeled Food, Names and Numbers.
On Spreadsheet B there are 3 sheets labeled Food, Names and Numbers.

I went ahead and tried to emulate what I am trying to do but reduce it vastly in scope. In short, I am trying to match the data from Spreadsheet A to Spreadsheet B - not just the first sheet but all sheets at the same time. I am looking to see what appears on Spreadsheet B that is also in Spreadsheet A and ignoring anything that is not the same.

The reasoning behind this is because I am trying to go through audit logs and am trying to compare the differences from week to week. I don't care as much about the new issues that pop up but moreso the reoccurring ones on a weekly basis.

I hope this was clear, if I could get any assistance or get pointed in the right direction it would be awesome. I am clueless as to who to efficiently use macros so if that's the direction I need to go, any and all guidance would be much appreciated. Thanks

Regards,

Usman