0
Thanks

A few words of thanks would be greatly appreciated.

Excel - Comparing columns from different sheets


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.
0
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Related

This document, titled « Excel - Comparing columns from different sheets », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).

0 Comments