Should I use Scripts or Add-ons to find all duplicates in a Column in Google Sheets? Not necessary! It’s quite easy if you use my custom formula and follow the tips below. You only need to spend 5 minutes to find all duplicates in a column and delete the entire row containing the duplicates. You can check a single column in thousands of rows with a single formula. What more! You can modify this formula to test two, three or any number of columns or even an entire row for duplicates.
How to Find All Duplicates in a Column and Delete Rows in Google Sheets
The term duplicates here indicates all the multiple occurrences of values in a column. In which the first occurrence is treated as unique and all other occurrences are treated as duplicates. See the example screenshot below. There Bill No. II – 10001 in row two is unique and its second and third occurrences in row 5 and row 8 are duplicates.
Additionally, my formula is not going to test only single value in an entire column for identifying duplicates. It checks all the values simultaneously and identifying all the duplicates. In the below example row three bill no. II – 10002 has also a duplicate in row five.
The Formula to Mark Duplicates in A Column in Google Sheets
=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),))
How to use this custom formula to find all duplicates in a Column in Google Sheets and delete the entire row that containing the duplicates?
Steps:
Insert a new column in your sheet or use the last blank column in your data set.
Here I am using Column F for this purpose. I’ve applied the above Google Sheets formula, that finds all duplicates in a Column (here Column C), in Cell F2.
This custom formula puts the value 0 in all the cells in Column F that has a duplicate in Column C. So you have now identified all the duplicates in Column C. Now the steps to delete these duplicates.
You have learned how to find all duplicates in a column in Google Sheets. Now how to delete all these duplicates at a time?
Steps to Delete Duplicates
First, select the entire data range.
Then Go to the menu Data > Create Filter. This will apply the filter to your data.
Filter the Column F for the value 0.
Now you have all duplicate rows in your sheet filtered. Select the entire rows and right click and delete.
Now go to the Data Menu and Turn Off filter. All the rows containing the duplicates have gone! Can I use this formula in multiple columns? Yes! You only need to make minor changes to the code.
In the above formula change the column range C2: C. For example, if the duplicates are in Column A and Column B, use should replace C2: C with the following piece of the formula.
concat(A2:A,B2:B)
Please follow the above steps carefully. First, apply this duplicate removal formula in a sample sheet and make sure that it produces the correct results that you expect. Then you can use it in your original sheet to find all duplicates in a Column and Delete the Row. Enjoy!
