Excel compare cells in columns A and B, show in column C cells only unique to column A

Question

I know there's a formula for this in Excel but I can't get it right. I've got a list of 7000+ names in column A, and a list of 4000+ names in column B. Some are duplicates, appearing on both column A and column B. I want to filter and display the result in column C of those names that ONLY appear in column A. If a name appears in both column A and column B, I don't want it to show in column C. There are some close examples on here already but I can't seem to get the formulas adapted correctly to do this. Thanks for any tips; I'm kind of lost at the moment.

Answer 1

Excel can do this easily (assuming that there are no duplicates in either column).

1. Convert your data to a Table.
2. Add a helper column, with the following formula =MATCH([ColumnA],[ColumnB],0). This will look up each value in your first column (A) and if it finds a match in your second column (B), it will return its relative position (helpful for finding it if you need to). But more importantly, it returns #N/A for any values it doesn't find.
3. Filter for #N/A in Column C for values that appear in Column A, but not B.

By converting your data to a Table (Step 1), you won't need to fill down values (the Table does that) and you can automatically filter based upon the error value.

Answer 2

You can put in column C this formula, but you'll have to fill it down the height of column A, then you'll need to re-sort it to remove blanks (probably after pasting values).

=IF(ISERROR(MATCH(A1,$B:$B,0)),A1,"")

You'd be better off using advanced filters, I think.