Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.


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.

Dataset to Remove Duplicates Based on 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.

Use Advanced Filter to Remove Duplicates Based on Criteria in Excel

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

Advanced Filter Dialog Box to Remove Dupplicates on Criteria

  • 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

Employ COUNTIF Function to Remove Duplicates Based on Criteria in Excel

  • After that, press Enter.

Using Ampersand Operator to Combine Text to Remove Duplicates Based on Criteria in Excel

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

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

Using COUNTIF Function to Count Duplicates and Remove in Excel

  • Next, select the column header of the Count column.
  • Then, go to the Data tab.
  • Afterward, select Filter.

Filtering to Remove Duplicates Based on Criteria

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

Apply Remove Duplicate Command Based on Criteria in Excel

  • 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


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")

Utilize IF and COUNTIFS Functions to Remove Duplicates Based on Criteria in Excel

  • 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")

Remove All Duplicates Based On Criteria in Excel

  • After that, press Enter to get the result.

Nesting COUNTIFS Function in a IF Function to Remove Duplicates Based on Criteria in Excel

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

Dragging Fill Handle Down to Copy Formula and Remove Duplicates Based on Criteria in Excel

  • Then, add Filter by following the steps from Method-02.
  • Afterward, click on the Filter button.
  • Next, select Unique.
  • Then, select OK.

Filtering Unique Values to Remove Duplicates Based on Criteria in Excel

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

Apply Conditional Formatting to Remove Duplicates Based on Criteria in Excel

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

Highlighting Cells Rules to Remove Duplicates Based on Criteria in Excel

  • Now, the Duplicate Values dialog box will appear.
  • Select Duplicate.
  • Afterward, select OK.

Duplicate Values Dialog Box to Remove Duplicates Based on Criteria in Excel

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

Filtering by Color to Remove Duplicates Based on Criteria in Excel

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

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.

Utilize VBA Codes to Remove Duplicates Based on Criteria

  • 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

VBA Code to Remove Duplicates Based on Criteria in Excel

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

Saving VBA Code to Remove Duplicates Based on Criteria in Excel

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

Running Macros to Remove Duplicates Based on Criteria in Excel

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

Practice Sheet for 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.


Similar Articles to Explore

Asikul Himel
2 Comments
  1. Great vba code to remove duplicates

Leave a reply

ExcelDemy
Logo