CODEWITHSUNDEEP
excelexcel-formula
Bob
Bob

Reputation: 1396

Excel: Finding Difference in Two Lists

Say I have two lists in two columns in an Excel Worksheet...

Column A

Apple
Baseball
Carlos
Dad

and... Column B

Baseball
Dad
Visual
Basic

I'm trying to compare what is in column A with what is in column B to find out what is in column A but not column B.I have a formula that works here that I can put in column C

=IF(COUNTIF($B:$B, $A2)=0, "No match in B", "")

My issue is how do I apply that formula to every row where Column A has an entry, and while doing that changing the $A2 in the formula to the corresponding row it's in. I can manually do this by going through and copying and pasting the formula to column C and changing $A2 to $A3,$A4 etc. Hoping theres a quick fix here so I don't have to do it for 400 rows!

Upvotes: 1

Views: 1288

Answers (1)

Gowtham Shiva
Gowtham Shiva

Reputation: 3875

Enter the below formula in Column C and drag down. You formula can be much simplified as below,

=IF(COUNTIF(B:B,A1),"Match","No Match")

COUNTIF returns a true if a match has been found, or in other words if the result is greater than 0. Returns false (0) if there is no match. Hence this can be written as mentioned above. (Checking if it is equal to 0 would not be necessary)

You could just drag the fill handle to as many rows you have.

enter image description here

Upvotes: 3

Related Questions