Alacrify Ltd

Useful Excel Worksheet Comparison

Published on: 18th January 2010
By Alacrify



Useful Excel Worksheet Comparison

One of our current projects involves comparing multiple datasets from a csv database extract to show which records have been removed or updated. To do this there is an original worksheet that gives the default baseline data on a given date. Then a second worksheet is copied into this file for comparison. This happens on a weekly reporting cycle.

I hunted for a method to find any original records that had been deleted in the weekly report. I found a great little tutorial that did the trick on the Microsoft website:

http://office.microsoft.com/en-us/excel/HA011039151033.aspx

By looking up a matching value for each original primary id record in the weekly report the finished excel worksheet now shows the original records that are now "missing". It's a very helpful solution to a little tricky problem without resorting to a load of database coding.

This is the sort of formula I ended up with

=IF(ISNA(MATCH(A2,'report-26.01.10'!   $B   $2:   $B   $46,FALSE)),"Missing", "-")