Worksheet Comparison (additions/deletions included)
Posted on 2013-06-11
I need something to compare two excel worksheets and highlight differences. I’ve done a bit of searching and found some macro solutions for this, but none have worked so far.
So, can someone help me put together a macro to do this for me?
Two sheets to compare, each containing 10 columns. The uniqueness of a row is defined by 3 separate columns (If those three cells are the same, and any of the other 7 are changed then it's an update, otherwise it's a new line)
Note: there could be anything from 500 to 10000 rows in these sheets.
The difficulty in the comparison is that there could be any number of rows added to / deleted from the middle of the sheet. As in, a block of 50 rows could be removed. And a block of 100 rows added in another section.
I need an output that will highlight (or move to knew sheets) additions / deletions and modifications.
In any of the macros I’ve tried so far, the issue comes about when, for example, some rows are deleted in Sheet2, then the results for the rest of the comparison get messed up. The macro is comparing the wrong rows and every line after that is counted as a change.
I can give sample data if someone is willing to help, although I would rather keep the sample data private if that’s possible?