Compare 2 worksheets

A participant came to me at the end of the course and asked if there is a easier way to compare 2 worksheets. This seems like a simple task of using VLOOKUP. Just enter the VLOOKUP formula into a column in one of the worksheet and refer to the other worksheet for comparative values.That’s what usually people need.But for her problem, it is not so simple. Her worksheet which contains employee data (e.g. employee number, name,

reporting manager, departmet) has multiple columns and rows and all the cells need to be compared against the other worksheet. To add to the challenge, the rows are not in the same order, meaning that the employee in row 10 may not be presented in Row 10 of the other worksheet. So that is the best way to identify the changes between the 2 worksheets?

First, we have to identify the possible outcome when we do comparison. Taking the WK2 as the latest worksheet, we will have to find out if the data in the available in WK1, the earlier worksheet. If No, then highlight the cells. If yes, we have to compare the numbers in WK2 against the number in Wk2. If the two numbers are different, highlight the cell in WK2. If they are the same, usually the same, do nothing.

We can make use of conditional formatting for this purpose. Conditional formatting can be very powerful if it is used with formula, like in our case.

For the 1st scenario (the employee cannot be found i.e. new employee), we can make use of the ISNA formula and VLOOKUP. When we do VLOOKUP, the result returned is #N/A. By making use of the ISNA formula, we can turn the result into TRUE or FALSE. When ISNA is TRUE, VLOOKUP returns #N/A, we can highlight the cell with a colour (using conditional formatting).

For the second scenario, VLOOKUP will return a value from WK1. In this case, we should contimue to check if the result returned by the VLOOKUP formula is the same as the value in WK2. If they are the same, then leave it alone. In this case, we should set up the formula to return true only when the 2 results are not equal to other. Therefore, instead of putting equal sign between the value in WK2 and the VLOOKUP formula, we should put not equal to. Apply the conditional formatting to all the cells in the worksheet and you will see those cells not equal to the previous value highlighted.

To automate the entire worksheet so that it can be used over and over again, we can set the range using the OFFSET formula.

Return to the top of compare 2 worksheets.