ExcelBanter - Trying to compare worksheets.

example: yesterdays data in list1, todays data in list 2

list1 cells A1:C4
result
a b c -present in list 2
a a a -not in list 2
b c d -not in list 2
x b a -present in list 2

list 2 cells A1:C3
result
a x a -not in list 1
a b c -not in list 1
x b a -present in list 1

formula for list 1, cell d1, type:

=SUM((CONCATENATE(A1;B1;C1)=CONCATENATE(List2!$A$1 :$A$3;List2!$B$1:$B$3;List2!$C$1:$C$3))^2)

then press ctrl+shift+enter, copy by dragging to cell d2:d3

when u see 1, record matches one record in list 2 (may be in different line)

formula for list 2, cell d1, type:

=SUM((CONCATENATE(A1;B1;C1)=CONCATENATE(List1!$A$1 :$A$4;List1!$B$1:$B$4;List1!$C$1:$C$4))^2)

then press ctrl+shift+enter, copy by dragging to cell d2:d4

when u see 1, record matches one record in list 1 (may be in different line)

U see 1 only if everything is completely same for particular record from
both lists (when any error, like mistyping, double space etc., formula
returns 0)

"Nicholas" wrote:

I have a worksheet that has yesterday's sales information by item. I receive
another worksheet that has today's sales numbers by item in it.
A unique record is made up of 3 columns A,B,C.

I need to find the following:
1. I need to find out if a new record has been inserted (that doesn't
exist on yesterdays worksheet).
2. I need to find out if the unique record has a match
3. I would also like to know what records have been deleted from
Yesterday's Sales information.

Thanks