MrExcel Publishing
Your One Stop for Excel Tips & Solutions

comparing worksheets for non-duplicate rows

Posted by Raymond Redd on September 10, 2001 8:12 AM

I have 2 worksheets containing customer lists.
I need to compare the 2 sheets and arrive at a list of
rows which are not in both sheets.

What I want is a list of customers
which esist only in the original worksheet.



Posted by Don on September 12, 2001 6:23 AM

COUNTIF will do the job. If the customer identifier is in column A in both worksheets, then on the original worksheet in a blank column, cell B2, enter =COUNTIF([FileB]Sheet1!$A:$A,A2)
and copy the formula down.

Zeros in column B mean that the entry in File 1 does not appear in File 2.

The function does an exact match so be careful what you use for the customer identifier. A trailing space which doesn't show on the screen will make names different, so a customer number is preferable.