Compare data of two Excel Columns A B and show data of Column A that do not exist in B

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

User · Accepted Answer

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

User · Answer

All values of column A that are not present in column B will have a red background  Hope that it helps as starting point    Sub highlight missings   Dim i As Long  lastA As Long  lastB As Long Dim compare As Variant Range  A A   ClearFormats lastA   Range  A65536   End xlUp  Row lastB   Range  B65536   End xlUp  Row  For i   2 To lastA     compare   Application Match Range  a   amp  i   Range  B2 B   amp  lastB   0          If IsError compare  Then             Range  A   amp  i  Interior ColorIndex   3         End If Next i End Sub

User · Answer

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    Format   Conditional Formating    Select  Formula Is  from drop down menu Enter the following formula    ISERROR MATCH A1  B 1  B 10 0   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

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

Examples related to excel

Examples related to excel-2007

Examples related to worksheet-function