For Recruitment as well as other fields, we often use Google Forms to get candidate or customer data. The result will save to Google Sheets just like Excel is sure that data duplicates are inevitable. So how do I filter duplicate data in Google Sheets? Excel is easy to find online, but Google Sheets is different from filtering. Hieu will share with you in the first article of Minh Hieu HR page and of the sharing section in the industry.
Updated 28/02/2018: Filtering out data that appears 2 times onwards changes the way it is quick, accurate, easy to understand
In the list above there are many names that overlap. We can find duplicate data and filter them in many ways, depending on your needs.
Related posts
Highlight (find) duplicate data
To find duplicate data in Google Sheets, we select the column that contains the data range. Click Format select Format by condition.
In the Conditional formatting rules table, we fill in the formula as shown below.
=Countif(A:A; A1)>1
Note: if the ” ; ” sign is not true for your machine, correct it to ” , ” okay. Edit the letter A to the text of the column you are formatting in your file.
So you have easily seen the duplicate data, next we will filter them. There are two ways to filter duplicate data in Google worksheets.
Filter out duplicate or non-duplicate data
Hieu inserted above a row to name it. In the cell next to the first, type the formula as shown below, and then drag the formula down to the full column.
=countif(A:A; A2)=1
At this time TRUE is the data is not duplicated, and FALSE is the duplicate data. We click on the number 1 sequence to select the name range, click Data, and select Filter.
Next click the filter button. Now that we want to see that the data doesn’t match, we keep TRUE, and if we want to see the data overlap, we keep FALSE.
And the result is obtained when we retain TRUE – the data does not coincide.
But this way is not optimal. We can only see duplicates or not duplicates. What we dream of is a complete list of data, filtering away only the data that is repeated 2 times or more. Hieu assured this is the first article to appear on Google to help you do this. Haha
Filter out data that appears a second time onwards
Selecting the first cell next to the column containing the data, Hieu selects column B, the corresponding A2 will be B2. In this cell you fill in the formula as shown in the image below.
So the cells repeating the second time onwards will change to Tranngocminhhieu.com
=if(countif($A $2:A2; A2)=1; A2;” Tranngocminhhieu.com”)
Explain:
$A$2: is to fix unchanged
A2: will change when pulled down
“Tranngocminhhieu.com”: Whatever you want to write
Now to filter the data that appears for the first time, then add the filter button > un-select Tranngocminhhieu.com > OK
Great yet, hehe
As a result, you already have a full list of data, less to lose any data and not duplicate any data. You can also see which doses have duplicates in the highlighted cell.
Links
That’s wonderful, isn’t it? With the last filtering will help you do a lot of work, such as emailing or bulk messaging is too much. This is minh Hieu HR’s first article, how do you feel and how difficult it is to leave a comment.