Wednesday, July 15, 2015

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!

No comments:

Post a Comment