In another article here ( In Google Sheets, how to highlight duplicates using a simple formula ), we had discussed how to highlight duplicates using the MATCH() function. In this article, we will see how to find duplicates when there are more than one columns to search for duplicates.
Suppose you have a google sheet with a “primary email” and “secondary email” fields. You want to find the duplicate email address in either of the columns.
We can use the COUNTIF() function to count the cell that matches certain criteria.
In this case, we just compare the value of the cell with all cell values in a range.
For example
COUNTIF(C$2D13, C14)
searches for the value of C14 in the cell range C2D13
Then you can copy-paste the cell to the rest of the columns to search for each cell value in the range.
If you do the same for the second column(D), you will get the duplicate cells in the D column as well.
Then just combine the results using a formula like this:
COUNTIF(C$2D13, C14) + COUNTIF(C$2D13, D14)
This will show you all the cells that have at least one duplicate email address.
