This macro will compare two lists of data and match rows where the values in one or two columns in each list matches
Let's use the macro to find the differences.
The following steps to used to compare list of data between the columns
Download Excel Column Compare.zip file
Unzip the file in any drive
Double click on ‘ListCompare.xls’
Read the steps given in ‘Help’ sheet
Select ‘Sales Example’ sheet to understand the example
On Excel's menu, click on Tools / Macro / Macros or simply press ALT-F8 (Hold ALT down and press F8)
Select range of columns from first list into Range 1 field similarly select range of columns from second list into Range 2 field
Click on ‘Update Criteria’ button to display the values in criteria section
Select columns from Range1 Column and compare columns from Range 2 Column
Click ‘Compare’ button
After clicking on compare button, the macro with create a new Excel workbook and copy the two lists into a single worksheet. Any row that cannot be matched will be highlighted and you will see "No Match" in the column between the two lists. Use the No Match column to sort the data and quickly view all the unmatched items together.