matching/comparing lists

[Mortir]

I have the following two lists:

LIST 1
Product Date Quantity
A 11 Nov 2007 130
B 13 Nov 2007 150
C 15 Nov 2007 200
C 17 Nov 2007 250
B 19 Nov 2007 240

LIST 2
A 11 Nov 2007 130
B 13 Nov 2007 150
D 14 Nov 2007 1000
C 15 Nov 2007 200
D 16 Nov 2007 1000
C 17 Nov 2007 250
B 19 Nov 2007 240


List 2 is the same as list 1 except that it has two new inputs -
product D on 14th and 16th Nov, with the same quantities of 1000. How
could I compare the two lists so that I could easily identify the
changes that were made to the first list? Or to identify the new
inputs that were made...

thanks for your help

 

[Pete_UK]

Here's a relatively easy way:

I am assuming that List1 and List2 are in different worksheets in the
same workbook, and that those are the sheet names. Assume also that
the data occupies columns A to C on both sheets, and that you have a
header row in both sheets so that the data starts in row2.

In both sheets, add this formula to D2:

=A2&B2&C2

and copy down. A quick way to do this is to select D2 after entering
the formula and then double-click the fill handle (the small black
square in the bottom right corner of the cursor).

Then in E2 of Sheet1 (List1), enter this formula:

=IF(ISNA(MATCH(D2,List2!D$2$1000,0),"missing in List2","ok")

and a similar formula in E2 of List2 sheet:

=IF(ISNA(MATCH(D2,List1!D$2$1000,0),"missing in List1","ok")

Copy both formulae down (adjust the range reference first if you have
more than 1000 rows).

You could then apply a filter to column E in both sheets to look for
any "missing" messages.

Hope this helps.

Pete