Comparing Two Excel worksheets for missing rows and changes...

[gemcgraw]

I've searched the various posts and can't find anything similar or close. If there is, I apologize for this post.

I have to compare each week's employee listing to the previous week. In the comparison, I have to find any employee that has dropped off the sheet, new to the sheet and on top of that compare 47 cells across (for each emp) that might have changed from the previous week's. (i.e. address change, phone, emergency contact, etc.) We use multiple systems therefore I have to take these changes to those systems for updates. (I know, I know! I've been preaching better technology!)

I have played with using VLOOLUP which works to a point. I've tried MATCH and a few other tricks but the way I see it, I need to first identify anyone who has dropped off from the previous week, otherwise it creates chaos in the formulas! Any suggestions as to where I need to start and how I can compress this task into just 1 or 2 formulas without creating a separate sheet just to computate this? Much thanks!

 

[pbornemeier]

Welcome to the Board!

I don't see a way around adding a few worksheets.

I would have an "All Employees" sheet that lists everyone that is supposed to be in the current worksheet and use a VLOOKUP formula using each name on the All Employees sheet to search the current sheet names column. Any formula that showed an NA would indicate that the name was not in the current worksheet.

You could then add the names that were missing from the currnt worksheet, with the corresponding cells marked with an X (or something) to indicate known missing names. So a sort on the current worksheet would arrange the names in the same order.

Then sort the current and last weeks worksheets by name, and on the current worksheet, add conditional formatting to show a highlighted cell if the contents of the corresponding cell from the previous worksheet differed from the same cell in the current worksheet.

 

[gemcgraw]

Phil - thanks so much! I see where you are headed with that. I have resolved the issue with "dropped" or "new" employees. My only struggle now is;

1) I have an emp that dropped off the Current sheet.
2) The Previous sheet now has more rows than the Current Sheet.
3) By trying to match cells from Previous to Current for differences, it gets pretty intense. Because I matching back to the Previous sheet, I run into the person who is missing and the formulas start #NA'ing on me.

What is the best way, even using a 3rd sheet, to take the name, search the Previous sheet, find the person, then located the corresponding cell and compare the data from the Current sheet cell? I have scrapped so many methods as it gets deeper.

 

[pbornemeier]

Copy the previous sheet and rename it to Old
Do a VLOOKUP on the Old sheet emp names; use the current worksheet as the table source.
Delete any employees that are NA (employees not on current worksheet) from the Old worksheet.
Sort both worksheets by employee id (or name if no emp id available) - to ensure the same order.
I would then use conditional formatting on the current worksheet to compare each cell in it to the corresponding one on the Old worksheet.