Do you often find yourself dealing with duplicate data in your Google Sheets? Don’t fret! With the help of the Filter menu and a handy helper column, it’s actually quite easy to filter out and delete duplicates in your Google Sheets. In fact, using the Filter menu offers one clear advantage when it comes to dealing with duplicates. Can you guess what it is?
By filtering rows in a table that contain duplicates, you can easily identify and delete those unwanted duplicate entries. It’s like magic – with just a few clicks, all those annoying duplicates are gone!
But before we dive into the filtering process, let’s take a closer look at the different types of duplicates that can occur in spreadsheets.
Different Types of Duplicates in Spreadsheets
In this tutorial, we’ll explore different ways to handle duplicates in Google Sheets. Let’s start with duplicates in a single column.
Single Column Duplicates in Sheets
When a value appears more than once in a column, we consider the second appearance onwards as duplicates. In the example below, the rows marked as “duplicates” are the ones we want to filter out.
Multi-Column Duplicates in Sheets
In some cases, duplicates can occur across multiple columns. Take a look at the marked rows 2 and 6 in the example below. Row #6 is a duplicate of row #2, as they have the same title, year, and author.
Random Column Duplicates in Sheets
There may be instances where you want to search for duplicates in specific columns, ignoring the others. In the example below, we are only concerned with columns A and C. In this case, all the rows, except the first one, are duplicates.
To handle these different types of duplicates, we’ll make use of a helper column to assist us in the filtering process. The following examples will provide you with the relevant formulas to enter in the helper column.
Filtering Duplicates in Google Sheets and Deleting Them
As promised, we’ll now learn how to filter duplicates in Google Sheets using the Filter menu.
Filtering Duplicates in a Single Column and Deleting Them
Here are the steps to follow:
- Enter the formula below in cell B2:
=query(iferror(sort({row(A2:A)-row(A2)+2-match(sort(A2:A),sort(A2:A),0),SORT(ROW(A2:A)+2-row(A2)+2,A2:A,1)},2,1),),"Select Col1")
Alternatively, you can use the following Countifs formula, which follows the running count logic:
=ARRAYFORMULA(if(A2:A<>"",COUNTIFS(A2:A,A2:A,ROW(A2:A),"<="&ROW(A2:A)),))
This formula will generate numbers in the helper column (column B).
-
Select columns A and B. Then, go to the Data menu and select “Create a filter.”
-
Click the drop-down arrow in cell B1. Choose “Filter by condition” and select “Greater than.” Enter the number 1.
Following these steps will filter out duplicates in a single column.
Filtering Duplicates in Multiple Columns and Deleting Them
To filter duplicates across multiple columns, follow these steps:
- Enter the formula below in cell D2:
=query({A2:A,iferror(sort({row(A2:A)-row(A2)+2-match(sort(A2:A&B2:B&C2:C),sort(A2:A&B2:B&C2:C),0),SORT(ROW(A2:A)+2-row(A2)+2,A2:A&B2:B&C2:C,1)},2,1),)},"Select Col2 where Col1 is not null")
Alternatively, you can use the following simpler alternative formula (Countifs running count):
=ARRAYFORMULA(if(A2:A<>"",COUNTIFS(A2:A&B2:B&C2:C,A2:A&B2:B&C2:C,ROW(A2:A),"<="&ROW(A2:A)),))
Enter either of the above formulas in cell D2.
- Select columns A to D and apply the filter. Click the drop-down arrow in cell D1. Just like in the previous example, choose “Filter by condition” and select “Greater than.” Enter the number 1.
With these steps, you can filter out duplicates in multiple columns.
Modifying the Formula to Add/Remove Columns
If you need to add or remove columns from the formula, follow these guidelines:
- To exclude a column, simply remove the corresponding column references from the formula. For example, if you want to exclude column B, remove it from the formula.
- For example, the modified formula would be as follows:
=query({A2:A,iferror(sort({row(A2:A)-row(A2)+2-match(sort(A2:A&C2:C),sort(A2:A&C2:C),0),SORT(ROW(A2:A)+2-row(A2)+2,A2:A&C2:C,1)},2,1),)},"Select Col2 where Col1 is not null")
- The same modification applies to the Countifs formula:
=ARRAYFORMULA(if(A2:A<>"",COUNTIFS(A2:A&C2:C,A2:A&C2:C,ROW(A2:A),"<="&ROW(A2:A)),))
Feel free to add or remove any number of columns based on your specific dataset. This method allows you to quickly filter duplicates in Google Sheets and delete them in a flash.
Update: Remove Duplicates Menu Command
As a bonus, Google Sheets now has a new built-in tool that makes it even easier to eliminate duplicates. Check out our guide on How to Use Remove Duplicates Menu Command in Google Sheets for more details.
Conclusion
Dealing with duplicates in your Google Sheets doesn’t have to be a hassle. By leveraging the power of the Filter menu and incorporating a helper column, you can efficiently filter and delete duplicates. Whether you’re working with single-column duplicates, multi-column duplicates, or random column duplicates, the methods outlined in this article will help you clean up your data in no time.
For more helpful tips and tricks on Google Sheets and other productivity hacks, be sure to visit Crawlan.com. Happy filtering!
