Comparing 2 set of data from 2 different dates

In last Friday training, one of the participants came with an exceptionally interesting problem. On a weekly basis, she needs to donwload a spreadsheet from the HR systems which will list down the employees' profiles, such as employee ID, Name, Department, Reporting Manager, etc. Using the downloaded worksheet, she has to compare the details against the previous week worksheet and identify the changes that have taken place. The first answer i thought of is to use VLOOKUP. But the problem is there are more than 20 columns and any of these columns could be different from the previous week. And there are not just a few rows but a few thousand rows. So how on earth can we identify all the cells from 20 columns x say about 6000 rows, total of 120,000 cells? Impossible if we are to eye ball at every cells.

The solution called for the use of VLOOKUP formula using employee ID as the lookup value plus conditional formatting. Using a formula based conditional formatting option, we can input the VLOOKUP formula (to pick up the data from the previous week) and compare the result with the value in the current cell. (Take note that this is not the normal VLOOKUP formula. You have to setup the VLOOKUP formula so that it will return the value TRUE or FALSE, the basis for conditional formatting to work.) If the values are different between the 2 weeks, the cell will be highlighted with a colour. This will allow us to zoom in into the colored cells and work with them. Of course, this is based on the assumption that most of the cells would remain the same between the 2 weeks. We have to make the formula more dynamic and To make the formula dynamic, we have to add in the COLUMN formula so that it can automatically change it's value as you apply the conditional formatting to the right and down the worksheet).