Comparing 2 ranges?

  • Thread starter Thread starter calberto22
  • Start date Start date
C

calberto22

Guys, i'm completely stuck on this and hoping you can help. i have 2
columns with emails. i need to compare column B to Column A. if an
email that is in column B is also in column A, then i don't need it.
but if it's unique to column B (it's not in column A as well) then i
need that email. i can't for the life of me figure out how to do
this. i was doing an IF function, but it's not working. i was doing
this =IF(B2=A1:A443, 'NO', B2). so basically, if the email in cell B2
is found in the range A1 to A443, then type NO, if the email in B2 is
NOT found, then give me the email that is in B2. this function is not
working though. can you help me out?

Thank you

Carla
 
Assuming source data running in cols A and B, from row1 down
In C1: =IF(B1="","",IF(ISNUMBER(MATCH(B1,A:A,0)),"",ROW()))
In D1: =IF(ROW()>COUNT(C:C),"",INDEX(B:B,SMALL(C:C,ROW())))
Select C1:D1, copy down to last row of data in col B. Hide away col C. Col D
will return the required results, ie email in col B not found in col A, with
all results neatly bunched at the top
 
One way....
Paste this formula into C1 and copy down as far as needed.
=IF(ISNA(VLOOKUP(B1,A:A,1,FALSE))=TRUE,B1,"")

Rob
 
Back
Top