Compare Two Excel Worksheets and Find Matches and Differences
Originally published: 11/12/2018 12:31
Last version published: 22/01/2020 15:09
Publication number: ELQ-38982-6
View all versions & Certificate

Compare Two Excel Worksheets and Find Matches and Differences

Compare Two Excel Worksheets and Find Matches and Differences, this could be the ultimate tool you are looking!


With this workbook (Excel VBA), you can compare two similarly structured data sets and find out exact variances at Row level and then by each cell level.

This workbook has three sheets: one for old data (for example last months payroll data), One for New data (current months payroll data), and most crucial sheet: Variance sheets, where you will be able to find out exact variances at each Row and then by each Cell.

Both data sets which you are trying to compare has to have common KEY columns. For Example, if you are trying to analyse a payroll dataset, you will have two months payroll sheet, last months data and current month data. Both data (sheets) should have the same number of columns (column headings must be equal). Both datasets must have the standard key column, so in case of Payroll data, it would be Employee Code. One all is set, you have to hit Compare data button in the third sheet which is Variance, and you will have exact variance in the form of ADDED rows, REMOVED rows and Variance highlighted in each cell.

For payroll dataset scenario, you will see new employees, leaving employees and for remaining employees, you will see variances in other variables like Salary and allowances increase or decrease.

One Variance is available, there are the number of other analysis can be done using traditional excel functions, Power Query or Pivot tables.

This Best Practice includes
Excel VBA Workbook with detailed step by step insctructions on how to use it.

Acquire business license for $15.00

Add to cart

Add to bookmarks


Further information

Compare Two Similarly structured Datasets (sheets) and find out variances by Row and then by Cell using Excel VBA Workbook

Whenever you have a situation where you would like to compare two datasets and find out variances, by common KEY column and then by each row.

Large datasets having more than 100K rows, since this is an Excel-based solution, and spreadsheets applications (including Excel) has limitation and it won't be able to give you results as intended due to speed bottleneck when it comes to large datasets,

0.0 / 5 (0 votes)

please wait...