Sheet to sheet comparison

[Chris]

Please help if you can.

I tried the following formula: =IF(COUNTIF(Sheet2!$A:$A,A2)>0,"X","") and
could not get it to work.

I'd like to clarify my request: I have a list of unique names in column B,
sheet 2. I'm like to determine if those names appear anywhere on sheet 1
and, if so, place an indicator X or similar into an adjacent cell "C" on
sheet 2.

Similarly, I'd like to, in a separate function, mark adjacently those names
on sheet 1 that do not appear on sheet 2.

Thanks in advance for any assistance offered.

Chris

 

[Frank Kabel]

Hi Chris
what do you mean with anythere on sheet 1? the following tests if the
entry in column B appears in column A on sheet 1
Put this in C1
=IF(COUNTIF('Sheet1'!$A:$A,B1)>0,"X","")
and copy down

For the other way round try the following in column B on sheet 1
=IF(COUNTIF('Sheet2'!$B:$B,B1)>0,"","not in sheet 2")

you may have a look at
http://www.cpearson.com/excel/duplicat.htm#ExtractingCommon
http://www.cpearson.com/excel/duplicat.htm#InOneNotOther