Excel: Find differences between two columns

The problem is this... you have a column of stuff (in my case it is usually user names).  You do whatever you are supposed to do with that column of stuff but find that some of it is not valid data (user doesn't exist) so you create a second column of the data you know to be valid.  Now you want to compare those two columns and find the DIFFERENCE so that you know which pieces of the first column are not valid.

When I sought a solution on the internet, people responded with a whole bunch of ways to find the items that are in common between the two columns.  They did not seem to understand that I was looking for the difference.  So... for those of you who have ever needed to find the difference between two columns here you go:

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

This assumes that your two columns are column A (the orignal list) and column B (your shorter list of items that you know for a fact are in column A) Change the numeral 11 to whatever row is at the end of your second column (column B).

Put this function into another column (like column C) and then drag it down to fill the fields through the end of the sheet.  You will get results that look like this:

Column A	Column B	Column C
item1	item1
item2	item10
item3	item11
item4	item12	item4
item5	item13
item6	item15
item7	item2	item7
item8	item3
item9	item5	item9
item10	item6
item11	item8
item12
item13
item14		item14
item15

The third column, the one that tells me what is in column A but not in column B is the result that I'm looking for.  If you have this same issue then enjoy.

If this helped you then please give a thumbs up, or leave a comment, or link back to this blog.
Thanks!