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.
•
|
New! Comments
Have your say about what you just read! Leave me a comment in the box below.