When it comes to finding and removing duplicates in a column in Google Sheets, many users wonder whether they should use scripts or add-ons. But guess what? You don’t need either of them! With my custom formula and the helpful tips I’m about to share, you can find and delete all duplicates in just 5 minutes. And the best part? You can check thousands of rows in a single column using this simple formula. Plus, you can even modify it to test multiple columns or an entire row for duplicates!
How to Find and Delete Duplicates in a Column in Google Sheets
Duplicates refer to multiple occurrences of values in a column, where the first occurrence is considered unique and the subsequent ones are considered duplicates. Take a look at the example screenshot below. In this case, “Bill No. II – 10001” in row two is unique, while its second and third occurrences in rows 5 and 8 are duplicates.
But here’s the kicker – my formula doesn’t just test a single value against the entire column. It checks all the values simultaneously, making it easier to identify all the duplicates. In the image above, you can see that “Bill No. II – 10002” in row three also has a duplicate in row five.
The Formula to Find Duplicates in a Column
To mark duplicates in a column in Google Sheets, you can use the following custom formula:
=ArrayFormula(IF(LEN(A2:A),if( SORT(ROW(A2:A)-ROW(A2)+2,SORT(ROW(A2:A)-ROW(A2)+2,C2:C,1),1)-MATCH(C2:C,SORT(C2:C),0)=1,,0),))
Now, let’s see how you can actually use this formula to find and delete all duplicates in a column.
Steps:
-
Insert a new column in your sheet or use the last blank column in your dataset.
In this example, I’m using Column F for this purpose. I’ve applied the formula mentioned above to Cell F2.
-
Once you’ve applied the formula, you’ll notice that it puts a value of 0 in all the cells in Column F that have duplicates in Column C. This way, you’ll easily identify all the duplicates in Column C.
Now, let’s move on to deleting these duplicates.
Steps to Delete Duplicates
-
First, select the entire data range.
-
Go to the Data menu and click on Create Filter. This will apply a filter to your data.
-
Filter Column F for the value 0.
-
Now, you’ll have all the duplicate rows filtered in your sheet. Select the entire rows, right-click, and choose Delete.
- Finally, go to the Data menu and turn off the filter. Voila! All the rows containing duplicates have been removed.
You might be wondering if you can use this formula in multiple columns. Absolutely! All you need to do is make minor changes to the code.
In the formula mentioned earlier, simply change the column range C2:C to include the desired columns. For example, if the duplicates are in Column A and Column B, you should replace C2:C with concat(A2:A,B2:B).
Before applying this formula to your original sheet, I recommend testing it on a sample sheet to ensure it produces the desired results. Once you’re confident, go ahead and use it to find and delete all duplicates in a column. Happy sheeting!
This article was brought to you by Crawlan.com, your go-to resource for all things Google Sheets and online crawling.
