VBA to compare two worksheets

**Ironman** · 03-21-2013, 06:49 PM

Hi all -- I have a task to compare and note any differences between two worksheets (maybe by color codes). Both sheets will be similar but not exact in reference to columns and rows. We have one report coming out of one reporting tool (SAS) and another coming from Cognos. We have many reports to compare. But for the sake of this post, say I'm comparing employee data...each reports shows Employee ID, Name, Title, Department, Manager, Salary...etc. One report may have Title in column D where the other may have it in J. I'm not sure really how to do this, with Vlookup but it also need to find it anywhere in the other sheet....hope this makes sense. That is why I was thinking something with VBA but I'm not sure where to get started.

I'll have one workbook with two sheets, Sheet1 is Cognos, Sheet2 is SAS. I've included a test file with a few columns and rows...any suggestions would be great!! The actual files I'm working with can be from 100 rows and 10 columns to 15000 rows and 20 columns...As you'll see by my example, the column for Location is in column E and is called Physical Location, on the other sheet it's in column G and that is called Location_Desc. Again, another wrinkle in my task...Thanks to all for taking a look!

**xladept** · 03-21-2013, 09:12 PM

Hi Ironman,

I may have something for you tomorrow - will there be other "reporting tools" and do you have a preferred order for the headers???

**xladept** · 03-22-2013, 02:59 PM

Hi Ironman,

Paste the UDT portion up top just under the "Option Explicit"

This will create a sheet that compares the "tools" over and under - take a look and let me know which discrepancies to treat and how to treat them

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**xladept** · 03-23-2013, 03:20 PM

Hi Ironman,

See if this is what you want - it list only the discrepancies, so no lone ID's are represented - it should be quick enough for 15K rows, but if you up the rows by a magnitude - we may need another paradigm

Please Login or Register  to view this content.

BTW you still need the UDT for this version.

I decided to send the whole thing: HiresXP.xlsm

**Ironman** · 04-16-2013, 02:01 PM

Hi xLadept - - Thanks for doing that. I'm not 100% why it's error'in out on

Please Login or Register  to view this content.

Specifically where it reads: .Cells(i,2): The example I attached was just that an example. I have many reports to validate and each has it's own set of columns/headers...reports can differ in columns as well as length, from 10 rows to 20,000. Columns can be A to M or A to AD...I do appreciate you putting in the time to send off the example file.

Any other suggestions?