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).

Create a list with no blank cells

Somebody sent me an excel file containing a list of hospitals in Column A. In the first row, it listed 40 different types of rebates. For each hospital, only certain rebates were used.

He would like to create a dropdown list for each hospital, showing only the rebates that contain a value. For example, he would like the dropdown list to show only Rebate1 and 3 for hospital 1 because only these 2 rebate columns contains values. He asked if it can be done.

I have tried googling on whether I can create a list with no blank cells before and couldn't find a solution. But I deceided to try again. To my surprise, I managed to find a few websites that demonstrated how it can be done using formulas. One of them that explained how it can done can be found here.

I modified the formula to do it for columns. Then I setup the formula such that he can do a double selection, select an hospital first before showing the list of rebates. This involved the use of the all powerful OFFSET formula to complete the solution. To see how it works, download this excel file.