How to Remove Duplicates Based on Criteria in Excel

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.

Remove Duplicates Based on Criteria in Excel: 7 Easy Ways

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 contain 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.

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.

Here, the Ampersand Operator (&) joins all three texts and returns the combined text.

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.

Now, in the COUNTIF function, I selected E$5:E5 as the range and E5 as the criteria. The formula returns the number of cells in the range that match the criteria.

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.

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.

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.

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.

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.

Here, in the UNIQUE function, I selected cell range B4:D14 as array, FALSE as by_col, and FALSE as exactly_one. Now, the formula will remove the duplicate rows from all the columns in the selected array keeping the first value.

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.

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.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Removing Duplicates.xlsm

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.

<< Go Back to Remove Duplicates in Excel | Duplicates in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

How to Remove Duplicates Based on Criteria in Excel

Remove Duplicates Based on Criteria in Excel: 7 Easy Ways

1. Use Advanced Filter to Remove Duplicates Based on Criteria in Excel

2. Employ COUNTIF Function to Remove Duplicates Based on Criteria in Excel

3. Apply Remove Duplicate Command Based on Criteria in Excel

4. Utilize IF and COUNTIFS Functions to Remove Duplicates Based on Criteria

Example-01: Keep First Data and Remove Other Duplicates

Example-02: Remove All Duplicates

5. Apply Conditional Formatting to Remove Duplicates Based on Criteria in Excel

6. Use Excel UNIQUE Function to Remove Duplicates Based on Criteria

7. Utilize VBA Codes to Remove Duplicates Based on Criteria

Things to Remember

Practice Section

Conclusion

Related Articles

Leave a reply Cancel reply