It is easy to remove duplicates and clean up your Google Sheet using one of the formulas. The best functions to use to find duplicates in a Google Sheet is MATCH() function and the COUNTIF() function. We use one of these functions to search for the current cell value in all the previous cells. Then just copy the formula to the rest of the cells. The duplicates automatically pop out!
Highlight the duplicates
First, let us see how to use COUNTIF() to search for duplicates.
Suppose we have a spreadsheet with a list of contacts.
Make a copy of this sample sheet here
We don’t want to send multiple emails to the same person. So we want to highlight the duplicate emails and then remove the duplicate records.
In the 12th Row, Column D (that is, cell D12) Enter this formula:
=COUNTIF(C$2:C11,C12)
This formula searches for the value of cell C12 in the range C2 to C11 (all cells above it) Returns 0 if no match found and returns the number of matches otherwise.
That means, it returns greater than 0 for duplicates.
If you copy and spread the formula to the rest of the column, you can quickly detect the duplicates.
You can see that two cells have turned 1. That means those emails are duplicates. You can verify by searching those emails (do Edit → Find & replace and search for those emails).
Now that we have detected the duplicates. The next step is to clean up the Google sheet off the duplicates.
Remove Duplicates
One easy way would be to filter and delete those rows.
You can also use QUERY() function to get only the unique values to another sheet.
Create a new sheet and in the top left cell, enter the formula:
=QUERY(Sheet1!A2:D,"Select A,B,C WHERE D <= 0 OR D is null")
Another step you may want to do is to copy the unique values as “only values” to another sheet. This can be handy if you want to further edit the sheet.
Select the range from the unique values sheet. Then do “copy”. Create a new sheet then right click on the sheet and select Paste Special → Paste values only.
You can also hide the “original” (with duplicates) sheet and get it out of the way.