Asikul Himel
While working with a large dataset in Excel, there is a possibility that you are getting the same duplicate values from different rows or columns. Sometimes you may need to remove those duplicate values based on some specific criteria to get a clear concept of the worksheet. Excel provides some built-in features and formulas from which you can easily remove duplicates based on criteria. Today, in this article, we will learn some methods to remove duplicates based on criteria in Excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
7 Easy Ways to Remove Duplicates Based on Criteria in Excel
Consider a situation where you are given a dataset of some customer names and sold products in the “Customer Name” and “Item Sold” columns. Now in the dataset, these rows and columns are containing some duplicate values. You will need to remove those duplicate values based on some criteria in Excel.
1. Use Advanced Filter to Remove Duplicates Based on Criteria in Excel
One of the key features of the Advanced Filter is that it can filter out duplicate values very easily. Using that feature you can remove duplicates based on criteria from the dataset. Let’s learn this method.
Steps:
- Firstly, select the cell range where you want to remove the duplicates. Here, I selected cell range B4:D14.
- Secondly, go to the Data tab.
- Thirdly, select Advanced.
- Next, the Advanced Filter dialog box will appear.
- Select Copy to another location.
- Then, select the location where you want to copy the data.
- Afterward, check the Unique records only option.
- Further, press OK.
- Finally, you will see that the duplicates are removed from the dataset based on all three columns.
Read More: How to Remove Duplicate Names in Excel (7 Simple Methods)
2. Employ COUNTIF Function to Remove Duplicates Based on Criteria in Excel
The COUNTIF function can help you to find if there are any duplicates in the columns based on criteria. Then you can filter the result to remove those duplicates based on the specific criteria. Let’s see the steps.
Steps:
- In the beginning, select the cell where you want the Combined Text from three columns.
- Next, write the following formula in that selected cell.
=B5&C5&D5
- After that, press Enter.
- Next, drag the Fill Handle down to copy the formula.
- Now, you can see that I have copied the formula to the other cells.
- Afterward, select the cell where you want to Count the repeat. Here, I selected Cell F5.
- Then, in Cell F5 write the following formula.
=COUNTIF(E$5:E5,E5)
- Next, press Enter to get the result.
- Afterward, drag the Fill Handle down to copy the formula to the other cells.
- Next, select the column header of the Count column.
- Then, go to the Data tab.
- Afterward, select Filter.
- Now, you will see that filter has been added.
- After that, click on the filter button.
- Then, check 1.
- Finally, you will see that the duplicate rows are removed from the dataset based on all the columns.
Read More: How to Remove Duplicate Rows Based on One Column in Excel
3. Apply Remove Duplicate Command Based on Criteria in Excel
You can also remove duplicates based on criteria using the Remove Duplicates feature. The Remove Duplicates feature is an amazing feature by which you can easily remove your duplicates from the dataset based on criteria in Excel. Here we will use this feature to remove duplicates based on multiple columns.
STEPS:
- Firstly, select the cell range for your dataset. Here, I selected cell range B4:D14.
- Secondly, go to the Data tab.
- Thirdly, select Remove Duplicates.
- Consequently, the Remove Duplicates dialog box will appear.
- Check the My data has headers option.
- Then, select the Columns based on which you want to remove data.
- Next, select OK.
- Here, a message will show how many duplicates are removed.
- Next, select OK.
- As a result, you can see that the duplicates are removed from the list.
Read More: Excel VBA: Remove Duplicates Comparing Multiple Columns (3 Examples)
Similar Readings
- How to Remove Duplicates Using VLOOKUP in Excel (2 Methods)
- Fix: Excel Remove Duplicates Not Working (3 Solutions)
- How to Remove Both Duplicates in Excel (5 Easy Ways)
- INDEX-MATCH with Duplicate Values in Excel (3 Quick Methods)
4. Utilize IF and COUNTIFS Functions to Remove Duplicates Based on Criteria
We can use the combo of the IF and the COUNTIFS formula to find out the duplicate values based on criteria and then filter them. Let’s explore the examples of this method.
Example-01: Keep First Data and Remove Other Duplicates
In this example, I will show you how you can keep the first data and remove the other duplicates. Let’s see the steps.
Steps:
- In the beginning, select the cell where you want to remove duplicates.
- Then, write the following formula in the selected cell.
=IF(COUNTIFS($B$5:$B5,$B5,$C$5:$C5,$C5,$D$5:$D5,$D5)>1,"Duplicate Row","Unique")
- Next, press Enter to get the result.
🔎 How Does the Formula Work?
- COUNTIFS($B$5:$B5,$B5,$C$5:$C5,$C5,$D$5:$D5,$D5): Here, in the COUNTIFS function, I selected $B$5:$B5 as criteria_range1, $B5 as criteria1, $C$5:$C5 as criteria_range2, $C5 as criteria2, $D$5:$D5 as criteria_range3, and $D5 as criteria3. The formula returns the number of cells where all the criteria are met.
- IF(COUNTIFS($B$5:$B5,$B5,$C$5:$C5,$C5,$D$5:$D5,$D5)>1,”Duplicate Row”,”Unique”): Now, the IF function checks for the logical_test. If the test is True then it returns “Duplicate Row”. Otherwise, it returns “Unique”.
- After that, drag the Fill Handle down to copy the formula.
- Then, add Filter by following the steps from Method-02.
- Next, click on the Filter button.
- Afterward, select Unique.
- Finally, select OK.
- Now, you will see that you have removed the duplicates and kept the first data.
Example-02: Remove All Duplicates
Here, I will show you how you can remove all the duplicates which means all the values that appear more than once. Let me show you the steps.
Steps:
- First, select the cell where you want to remove the duplicates. Here, I selected Cell E5.
- Next, in Cell E5 write the following formula.
=IF(COUNTIFS($B$5:$B$14,$B5,$C$5:$C$14,$C5,$D$5:$D$14,$D5)>1,"Duplicate Row","Unique")
- After that, press Enter to get the result.
🔎 How Does the Formula Work?
- COUNTIFS($B$5:$B$14,$B5,$C$5:$C$14,$C5,$D$5:$D$14,$D5): Here, in the COUNTIFS function, I selected $B$5:$B$14 as criteria_range1, $B5 as criteria1, $C$5:$C$14 as criteria_range2, $C5 as criteria2, $D$5:$D$14 as criteria_range3, and $D5 as criteria3. The formula returns the number of cells where all the criteria are met.
- IF(COUNTIFS($B$5:$B$14,$B5,$C$5:$C$14,$C5,$D$5:$D$14,$D5)>1,”Duplicate Row”,”Unique”): Now, the IF function checks for the logical_test. If the test is True then it returns “Duplicate Row”. Otherwise, it returns “Unique”.
- Next, drag the Fill Handle down to copy the formula to the other cells.
- Then, add Filter by following the steps from Method-02.
- Afterward, click on the Filter button.
- Next, select Unique.
- Then, select OK.
- Finally, you can see that all the duplicates are removed from the list.
Read More: How to Remove Duplicates and Keep the First Value in Excel (5 Methods)
5. Apply Conditional Formatting to Remove Duplicates Based on Criteria in Excel
You can also use the Conditional Formatting feature to remove duplicates based on criteria. Here, I will use Conditional Formatting to remove all the duplicate values including the first occurrence. Let me show you the steps.
Steps:
- Firstly, get the Combined Text by following the steps from Method-02.
- Secondly, select the Combined Text.
- Thirdly, go to the Home tab.
- Afterward, select Conditional Formatting.
- Consequently, a drop-down menu will appear.
- Select Highlight Cells Rules.
- Then, select Duplicate Values.
- Now, the Duplicate Values dialog box will appear.
- Select Duplicate.
- Afterward, select OK.
- Now, you will see that the values that appear more than once are highlighted.
- Next, add Filter by following the steps from Method-02.
- Then, click on the Filter button.
- Select Filter by Color.
- Afterward, select No Fill.
- Now, you will see that all the duplicates are removed from the list.
Read More: Excel VBA: Remove Duplicates from an Array (2 Examples)
6. Use Excel UNIQUE Function to Remove Duplicates Based on Criteria
You can also use the Excel UNIQUE function to remove duplicates based on criteria and get the unique values from a list. Let’s see the steps of this method.
STEPS:
- To begin with, select the cell where you want the unique list. Here, I selected Cell F4.
- Then, write the following formula in Cell F4.
=UNIQUE(B4:D14,FALSE,FALSE)
- Next, press Enter and you will get the unique list.
- Finally, I formatted the unique list according to my preference.
7. Utilize VBA Codes to Remove Duplicates Based on Criteria
You can also use a VBA code to remove duplicates from the dataset based on criteria. Here, I will remove the duplicate rows using a VBA code. Let’s learn this method.
Steps:
- Firstly, go to the Developer tab.
- Secondly, select Visual Basic.
- Consequently, the Visual Basic Editor window will appear.
- Select the Insert tab.
- Next, select Module.
- Now, a Module will appear.
- Write the following code in the Module.
Sub Removing_duplicate_rows()
Dim selected_rng As Range
Set selected_rng = Selection
selected_rng.RemoveDuplicates Columns:=Array(1), Header:=xlYes
End Sub
🔎 How Does the Code Work?
- Here, I created a Sub Procedure named Removing_duplicate_rows.
- Then, I declared a variable named selected_rng as Range.
- Next, I used the Set Statement to set the selected_rng as Selection.
- Afterward, I used the RemoveDuplicates Method to remove duplicate rows from the selected range.
- Finally, I ended the Sub Procedure.
- After that, Save the code and go back to your worksheet.
- Afterward, select the data range from where you want to remove duplicates.
- Next, go to the Developer tab.
- Then, select Macros.
- Next, select Removing_duplicate_rows as Macro name.
- Then, select Run.
- Subsequently, you will see that the duplicate rows are removed from the data range.
Read More: How to Remove Duplicates in Excel Sheet (7 Methods)
Things to Remember
- When you select your Range, you have to use the absolute cell references ($) to block the array.
- You can filter your data in other places in the worksheet while using the Advanced Filter option.
Practice Section
Here, I have provided practice sheets for you to practice how to remove duplicates based on criteria in Excel.
Conclusion
In this article, I have shown four simple yet effective methods to remove duplicates based on criteria in Excel. I hope this article proves useful to you. If you have any ideas or suggestions, you are welcome to share your thoughts in the comment box.
Great vba code to remove duplicates
Thanks, SANDEEP! Best regards!