To compare two lists and extract the differences you can use the COUNTIF and IF function combination in Google Sheets. It’s two dimensional.
For example, I have taken quotations from two vendors for plumbing materials. Both of the vendors provided their quotation based on the availability of materials with them. Now I can compare these two lists in different ways.
- Compare the list provided by vendor 1 with my original requirement and find the nonavailable items.
- I can also find the items in the provided list of vendor 1, that do not match my requirements.
This test is also required as sometimes the vendor may offer alternate items in his list.
I hope the following comparison can give you more insight regarding this.
My requirements are in Column A and what the vendor 1 offered (quoted) is in Column B.
Since it’s a small list, you can manually find the items that are not in the offered list. With the help of my custom Google Sheets formula, I’ve found that in Column C.
In Column D, I have listed the additional items that the vendor offered, which are not in my original requirement in Column A.
In Google Sheets, you can easily compare two lists and extract the differences. Here is that formula.
The Formula to Compare Two Lists and Extract the Differences in Google Sheets
The Formula in C2:
Compare column A with column B and return the items in column A that is not in column B.
Formula:
=ArrayFormula(sort(if(COUNTIF(B2:B,A2:A)=0,A2:A,)))In this case, you should use the Column A values as the conditions in Countif. That’s important.
This formula follows the below COUNTIF syntax.
COUNTIF(range, criterion)The COUNTIF formula returns a conditional (A2:A contains the conditions) count across a range (B2:B is the range).
This conditional count formula returns the value 1 or more for matches and 0 for differences.
We can use the IF function to return the items in column A wherever the count is 0.
Reference: Google Sheets Function Guide by Info Inspired
The Formula in D2:
Compare column B with column A and return the items in column B that is not in column A.
Here, we should use the Column B value as the Countif condition.
=sort(if(COUNTIF(A2:A,B2:B)=0,B2:B,))This way you can compare two lists and extract the differences in Google Sheets.
Question:
I have a list of names in Column Y and another list in Column Z. I want to find the names in Column Y that are not in Column Z. In this case, which columns should I use as the Countif Rage and Condition?
Answer:
No doubt the conditions should be the column Y range in the Countif. Then obviously the data range would be the Column Z range.
Before winding up this tutorial I am sharing one more formula with you.
Want to find the matches in two lists?
=sort(if(COUNTIF(B2:B,A2:A)=1,A2:A,))This formula compares the list 1 with list 2 (Colum A with Column B) and extracts the common items.
Similar Google Sheets Formula Examples:
Hey, Thank you.
What if the item repeats?
Say, for example, Item 6 is twice in column A and only once in column B.
Hi, Mithun P R,
In the formula, replace the first occurrence of A2:A with
A2:A&COUNTIFS(row(A2:A),"<="&row(A2:A),A2:A,A2:A)Similarly replace B2:B with
B2:B&COUNTIFS(row(A2:A),"<="&row(A2:A),B2:B,B2:B)Formula in C2:
=ArrayFormula(sort(if(COUNTIF(B2:B&COUNTIFS(row(A2:A),"<="&row(A2:A),B2:B,B2:B),A2:A&COUNTIFS(row(A2:A),"<="&row(A2:A),A2:A,A2:A))=0,A2:A,)))I have actually added a running count of A2:A values to make them unique.
You may also like this - Make Duplicates to Unique by Assigning Extra Characters in Google Sheets.
Great article, helped me a lot!
But although I could do what I intended in my project, I still don’t understand one thing. Could you explain this or direct me to some reading that does?
In the formula:
=sort(if(COUNTIF(A2:A,B2:B)=0,B2:B,))The IF returns blank if there is a difference and it is false, doesn’t it?
Then the SORT returns the list of values that are different. I don’t understand how the SORT function gets these values or what the IF is actually returning as value_if_false.
Hi, gustavo,
To understand the logic, please make a table as per my example (cell range A1:B7).
Then in cell H2, insert the below Countif.
=ArrayFormula(COUNTIF(A2:A10,B2:B10))Note:- You won’t see ArrayFormula use in my original formula. I’ll come to that below.
You can see that it returns the numbers 1 and 0 (against B2:B10). The number 1 for the match and 0 for the mismatch.
The logical_expression in IF is
ArrayFormula(COUNTIF(A2:A10,B2:B10))=0value_if_true – return values from B2:B10
value_if_flase – return blank
The SORT plays two roles.
1. It’s an alternative to the above ArrayFormula use. So COUNTIF works without any issue in a cell range.
2. The second one is most important. Without SORT, you may have blanks between the values in the IF result. By sorting, we can solve it.
This is a great help, thanks for the info. Could you help with how to get the count of the number of items in column A which are not present in Column B?
=counta(A2:A)-sum(ArrayFormula(countif(A2:A,B2:B)))