Excel - Comparing columns from different sheets

August 2017

Issue

I have column A in datasheet 1 which has a list of names.
In Column A of datasheet 2 I have a much longer list of names. Some of the names are in both columns from both sheets. I am looking for a formula that will produce in column A of datasheet 3 the names that appear only in sheet 2.

Solution

In sheet2 in B2 (row one is column headings in both sheets)
• Type this formula
• `=IF(ISERROR(A2=Sheet1!\$A\$2:\$A\$10),"NO","YES") `
• While writing the formula take the cursor to sheet1 and draw it whole data range in column A so that

\$A\$2:\$A\$10 will be correct range.
• convert these references to absolute ref.
• Copy B2 down .
• The easy way is take the cursor to right bottom B2 when cursor changes into bold plus sign.
• Click on the + sign.
• Now filter sheet according to column B criteria being "NO" .
• In the filtered data copy column A and paste it in sheet 3.

Thanks to Venkat1926 for this tip.

Related

Published by aakai1056. Latest update on October 17, 2014 at 10:30 AM by Jeff.