In this article, we would have a look at how to compare columns in Google Sheets to look for matching and non-matching values.
Sometimes you would want to compare two columns and spot the differences between the two. For example, while comparing shopping lists for two individuals, you might want to look at the common item or unique items. Another scenario might be comparing responses of a form to compare to commonly used responses.
Compare columns in Google Sheets per row
Let’s say we have a sheet that contains a shopping list of two individuals as columns.
We want to check what items are common in order. It is pretty simple as we have to check the equality for the adjacent cells. For example, compare A2 to B2, A3 to B3 and so on. To do so we can just use the formula “=A2=B2” (to compare A2 and B2). It would return True if the values are equal and False otherwise. Applying this to every row we get the following result.
Displaying message for equality
Displaying true and false works, but it is a very bad design in terms of readability and user friendliness. What we can do to improve it is display messages “Equal” and “Not Equal” for true and false values respectively. To implement the same, we will use the conditional formula IF.
For the first cell, the formula would be “=IF(A2=B2, “Equal”, “Not Equal”)”. This would print “Equal” if A2=B2, otherwise it would print false. Do this for every row and you’ll the following final result.
You can read more about logical functions here
Highlight cells based on Equality
Rather than printing “Equal” or “Not equal”, a better way would be to highlight cells based on equality. This can be achieved using Conditional Formatting. Conditional formatting allows you to format cells based on any condition you want. Here were would compare two cells and make the cell color light green to indicate that matches.
To do so follow the given steps
- Select the second column
- Go to Format>Conditional Formatting
- A sidebar like such would open up.
- In the “Format cells if”, select the Custom Formula.
- Another field would appear asking for the formula.
- Enter “=$A2=$B2” and then select the color in the next field.
- Click Done.
You’ll notice that the adjacent cells with equal values would get colored in green.
If, you want to highlight the unequal values then you can use the formula =$A2<>$B2. Here <> is the inequality operator.
Compare columns in Google Sheets irrespective of the order
So far, we have been comparing cells based on the equality of the adjacent cells, but in real life the two shopping lists can be of different order, i.e. it is not necessary that equal items would be adjacent to each other. What do we do in this case. Here we can write our custom formula bit differently. We would search each cells value in the entire other column to see if they exist there. To do so we would make use of the COUNTIF function. You can read more about COUNTIF function here.
This time in the Custom formula section in Conditional Formatting side bar, enter the formula
“=COUNTIF($A$2:$A$11,B2)>0”
As you can see, some entries which aren’t adjacent but appear in the first column have been highlighted.
Note: If you want to highlight the entries which are not present in the first columns, you’ll have to enter the formula “=COUNTIF($A$2:$A$11,B2)=0”
Conclusion
We saw how to compare columns in Google Sheets to check for matching or different entries. We also checked for entries that are not adjacent and performed a search for them as well. We made use of different Google Sheets formulas which you can go through to get a better understanding of the entire topic.
See Also
You can check out other equally good articles on Google Sheets here.
Delete Empty rows in Google Sheets: In this article, we would see how to delete empty rows in Google Sheets using two different ways.
How to group rows in Google Sheets: We will learn how to group rows in Google Sheets. We will also have a look at nested grouping.