Ever had to compare large lists of data in Excel? It can be a nightmare!! Imagine you have two lists of annual sales transactions from different sources. The lists should be identical, but the one contains 30,000 transactions and the other contains 30,006 transactions. How do you find the extra 6 transactions? And what if you find those 6 transactions, but the sales figures still don't match? Maybe each lists contains a couple of rows that does not appear in the other list.

Use this macro to quickly compare two lists of data and highlight rows that do not have an equivalent in the other list based on 1 - 3 columns within the lists.

In our sales transactions example, you may tell the macro to match rows based on an Invoice number and an Item number, for example. The macro will create a new spreadsheet containing both lists. Rows from the original lists that can be matched will apear on the same row in the new spreadsheet and rows that cannot be matched with be highlighted and marked. This way you can easily sort the data according to the marked column and view all unmatched data together.

As an example the macro took less than 2 minutes to compare two lists of approximately 40,000 rows and 25 columns each. It then only took me a couple of seconds to sort the new spreadsheet and find all the unmatched rows.

The spreadsheet contains an example to take you step by step through using the macro.

Tested on Excel 97 and XP

Now compares on up to 3 columns

Download the List Compare Macro (49kb)

Any feedback or ideas will be greatly appreciated.