Macro that compares two worksheets by ID and outputs changes

**akh** · 10-01-2010, 04:23 AM

Hey there,

I'm not 100% sure this is easy to do in Excel, but I know it's possible in MySQL.

Basically, I'm looking for a macro that can loop through two different workbooks (it's OK if I have to put the worksheet names to compare in the code manually) comparing line for line, not by line number, but by a unique ID that will be living in column A.

Ideally, afterwards this would output which lines deviate, and which column in particular does so.

If need be, this can also be put together into one workbook, and I could copy all the data into two worksheets and we could keep it all within one workbook.

The reason for this is basically comparing data to historic data and seeing if there are differences.

If my description isn't clear please let me know - I'll give whatever details I can.

Best,
Andy

**Andrew-R** · 10-01-2010, 05:23 AM

You could try this...

Please Login or Register  to view this content.

**akh** · 10-01-2010, 09:13 AM

Andrew - Awesome. Unfortunately it looks like im buried until the end of the day, but I'll play with it over the weekend and hopefully by Monday I'll be able to tell you how awesome this works.

Best,
Andy

**Andrew-R** · 10-01-2010, 09:27 AM

OK. It's untested and coded off the top of my head, so let me know if you find any bugs and I'll sort them out.

**akh** · 10-19-2010, 10:21 AM

Hey Andrew (or whoever else feels like helping),

After wondering like an idiot why I was getting "run time error 9 subscript out of range", I realized I had to have the two to be compared files open.

I succesfully ran the macro, and I intentionally planted differences in the two files. Unfortunately there was no output on the macro. Any idea why?

Thanks a lot in advance!

Best,
Andy