For recruitment and other fields, we often use Google Forms to get candidate or customer data. The results will be saved to Google Sheets like Excel, so data duplication is inevitable. So how to find duplicates in Google Sheets? Excel is easy to find solutions on the internet, but Google Sheets has a different way of filtering. I will share with you my best ways in this post.
There are many duplicate names in the list above. We can find duplicates and filter them in many ways, depending on your needs.
Highlight duplicates in Google Sheets
To find duplicate data in Google Sheets, we select the column containing the data range. Click Format, select Conditional Formatting.
In the Conditional Formatting Rules table, we fill in the formula as shown below.
=Countif(A:A;A1)>1
Note: if the semi-colon is not correct for your device, please change it to the comma. Change the letter A to the letter of the column you are formatting in your file.
So now that you can easily see the duplicate data, we will filter them next. There are 2 ways to find duplicates in Google Sheets.
Find duplicates or unique in Google Sheets
I insert a row above for naming. In the first next cell, enter the formula shown below and drag the formula down the entire column.
=countif(A:A;A2)=1
TRUE is the data that does not overlap, and FALSE is the data that does not match. We click on range number 1 to select the range of names, click Data, and select Filter.
Next, click the filter button. Now we want to see data that do not match, keep TRUE, and see duplicate data, keep FALSE.
And the result is obtained when we keep TRUE – the data does not match.
But this way is not optimal. We can only see if it matches or doesn’t match. We dream of a complete list of data, only filtering out the duplicated data twice or more. I guarantee this is the first article that appeared on Google to help you do this. haha
Find duplicates in Google Sheets appear 2 times
Select the first cell next to the column containing the data. I select column B, corresponding to A2 will be B2. In this cell, you enter the formula as shown below.
Thus, the cells that repeat the second time onwards will change to Trangngocminhhieu.com.
=if(countif($A$2:A2;A2)=1;A2;”Tranngocminhhieu.com”)
Explain:
$A$2: is to be fixed without changing
A2: will change when scrolling down
“Tranngocminhhieu.com”: You can write whatever you want
To filter the data that appears for the first time, check the filter button > uncheck Trangngocminhhieu.com > OK.
Isn’t that great, hehe
As a result, you have a complete list of data without any loss of data and no duplicate data. You can also see which data matches in the highlighted box.
Remove duplicate data on Google Sheets
Google Sheets now has a feature to remove duplicate data. You just need to select the column you want to delete duplicate data > Data > Data cleanup > Remove duplicates.
Epilogue
Too great, is not it. The final filter will help you do many of those tasks, such as sending emails or mass messaging. I suggest one more article related to this topic: merging multiple Excel and CSV files into 1 Sheet. How do you feel about the above methods and how difficult it is? Would you mind leaving a comment below the article?
