Originally published: 11/12/2018 12:31
Last version published: 14/03/2019 16:30
Publication number: ELQ-38982-2
View all versions & Certificate

# Compare Two Datasets or Worksheets with Excel VBA Workbook

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

Description
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 business tool includes
Excel VBA Workbook with detailed step by step insctructions on how to use it.

Discuss

## Further information

### Objectives

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

### Use it if

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.

### Don't use it if

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,

## Reviews

• No review yet!

## Any questions on Compare Two Datasets Or Worksheets With Excel VBA Workbook?

The user community and author are here to help. Go ahead!