Compare data of two Excel Columns A & B, and show data of Column A that do not exist in B [closed]

Question

I have an Excel file that has columns A and B, both have data that may or may not exist in other column, I'm only interested in the data of column A that do not exist in B. These Columns have same Header names. What formula can I use to show which items in column A aren't in B?

Answer 1

Put this in C2 and copy down

=IF(ISNA(VLOOKUP(A2,$B$2:$B$65535,1,FALSE)),"not in B","") 

Then if the value in A isn't in B the cell in column C will say "not in B".

Answer 2

Suppose you have data in A1:A10 and B1:B10 and you want to highlight which values in A1:A10 do not appear in B1:B10.

Try as follows:

1. Format > Conditional Formating...
2. Select 'Formula Is' from drop down menu

3. Enter the following formula:

   =ISERROR(MATCH(A1,$B$1:$B$10,0))

4. Now select the format you want to highlight the values in col A that do not appear in col B

This will highlight any value in Col A that does not appear in Col B.