How to Remove Duplicates Based on Criteria in Excel (4 Methods)

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 we may need to remove those duplicate values based on some specific criteria to get a clear concept about 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 sheet to exercise while you are reading this article.


4 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. We need to remove those duplicate values based on some criteria. To do that, we will use four different criteria discussed below.

Remove Duplicates Based on the Advanced Filter in Excel


1. Remove Duplicates Based on Criteria with the Advanced Filter

One of the key features of the advanced filter is that it can filter out duplicate values very easily. Using that feature we can remove duplicates from the dataset. Let’s learn this method!

Step 1:

  • To remove duplicates based on criteria using the advanced filter, select the whole dataset, go to the Data tab, then in the Sort & Filter group, click on Advanced.

Data → Sort & Filter → Advanced

Remove Duplicates Based on the Advanced Filter in Excel

Step 2:

  • In the Advanced Filter window, check on Filter the List, in-Place to filter the dataset in its current location. Select your List-Range Check on Unique Records Only. Click OK to get the result.

Remove Duplicates Based on the Advanced Filter in Excel

  • Our dataset is filtered and the duplicates are removed based on the selected criteria.

Remove Duplicates Based on the Advanced Filter in Excel

Step 3:

  • If we want to make our unique dataset list in another place in the worksheet, just click on Copy to Another Place in the Advanced Filter Select your place by inserting values in the Copy to box. Click OK to continue.

Remove Duplicates Based on the Advanced Filter in Excel

  • Our duplicate values from the dataset are removed and a new unique dataset is created.

Remove Duplicates Based on the Advanced Filter in Excel

Read more: How to Remove Duplicate Rows in Excel


2. Remove Duplicates Based on Columns in Excel

We can remove the duplicates based on single or multiple columns in Excel. These procedures are discussed below.

i. Remove Duplicates Based on a Single Column

The COUNTIF function can help you to find if there are any duplicates in the columns. Then you can filter the result to remove those duplicates based on the specific column.

Step 1:

  • Create two new columns named Combined Text and Count in the existing dataset. Here we will apply the formula and filter the duplicate values based on criteria.

Remove Duplicates Based on a Single Column in Excel

Step 2:

  • In cell D4 of the Combine Text column, apply this formula,
=B4&C4&D4
  • This formula will add cells B4, C4, D4

Remove Duplicates Based on a Single Column in Excel

  • Press Enter to apply the formula and copy this formula down to the last cell. Now we will remove duplicates from the dataset based on this new Combined Text column

Remove Duplicates Based on a Single Column in Excel

  • Now we will apply the COUNTIF function in cell F4. The formula is
=COUNTIF(E$4:E4, E4)
  • Where E$4:E4 is the range and the criterion is F4.

Remove Duplicates Based on a Single Column in Excel

  • Get the result by pressing Enter. Apply the same function to the rest of the cells. If the function returns more than one value then the value is a duplicate. That’s how we will find out the duplicates in the dataset.

Remove Duplicates Based on a Single Column in Excel

Step 3:

  • So we have got the status of our dataset. Now we will filter the dataset. Click on the Count column header and go to Sort & Filter in the Editing Group and click on Filter

Home → Sort & Filter → Filter

Remove Duplicates Based on a Single Column in Excel

  • Now we have the filter option in every column of our dataset.

Remove Duplicates Based on a Single Column in Excel

  • Click on the drop-down filter icon in the Count From the given option, uncheck 2 to filter the dataset, and click OK.

Remove Duplicates Based on a Single Column in Excel

  • We have successfully removed the duplicates from the dataset based on a single column.

Remove Duplicates Based on a Single Column in Excel


ii. Remove Duplicates Based on Multiple Columns

We can remove duplicates based on criteria using the Remove Duplicates feature. The Remove Duplicates is an amazing feature by which you can easily remove your duplicates from the dataset based on criteria. Here we will use this feature to remove duplicated based on multiple columns.

Step 1:

  • Select the whole dataset. Go to the Data tab then select the Remove Duplicates option in the Data Tools

Data Remove Duplicates

 Remove Duplicates Based on Multiple Columns in Excel

Step 2:

  • In the Remove Duplicates window, check on the My Data has Headers We want to remove our duplicate values based on the Customer Name and the Item Sold column. Make sure to check on those two columns and then click OK to continue.

 Remove Duplicates Based on Multiple Columns in Excel

  • A new window appears telling that our duplicate values are removed. Click OK to proceed

 Remove Duplicates Based on Multiple Columns in Excel

  • Now we have successfully removed all the duplicate values based on multiple columns.

 Remove Duplicates Based on Multiple Columns in Excel


Similar Readings:


3. Remove Duplicates Based on Rows in Excel

We can use the combo of the IF and the COUNTIFS formula to find out the duplicate values based on wors and then filter them. Let’s learn this method!

Step 1:

  • Create a new column named Status in the existing dataset. Here we will apply the formula and filter the duplicate rows.
  • In cell E4 of the Status column, combine the IF and the COUNTIFS Insert the values into the formula and the final form is,
=IF(COUNTIFS($B$4:$B4,$B4,$C$4:$C4,$C4,$D$4:$D4,$D4)>1,"Duplicate Row","Unique")

Remove Duplicates Based on Rows in Excel

  • Where $B$4:$B4, $C$4:$C4, $D$4:$D4 are the ranges
  • $B4, $C4, $D4 are the criteria
  • If the argument is true then the formula will return Unique
  • If the argument is false then the formula will return Duplicate Row
  • Press Enter to apply the formula.

Remove Duplicates Based on Rows in Excel

  • And apply the same to the rest of the cells.

Remove Duplicates Based on Rows in Excel

Step 2:

  • So we have got the status of our dataset. Now we will filter the dataset. Click on the Status column header and go to Sort & Filter in the Editing Group and click on Filter

Home → Sort & Filter → Filter

  • After adding the filter to the dataset, click on the drop-down filter icon in the Status column. From the given option, uncheck Duplicate Row to filter the dataset and click OK

Remove Duplicates Based on Rows in Excel

  • Our duplicate values are now filtered out based on rows

Remove Duplicates Based on Rows in Excel

Step 3:

  • In the previous steps, we removed duplicates based on rows without the first occurrence. You can easily filter the duplicates based on rows including the first occurrence too. To do this, the formula is,
=IF(COUNTIFS($B$4:$B$13,$B4,$C$4:$C$13,$C4,$D$4:$D$13,$D4)>1,"Duplicate Row","Unique")
  • Where the new range is $B$4:$B$13, $C$4:$C$13, $D$4:$D$13. And the rest arguments are the same as before.

Remove Duplicates Based on Rows in Excel

  • Press Enter to apply this function and apply the same to the rest of the columns.

Remove Duplicates Based on Rows in Excel

Step 4:

  • We will now filter them as we used in the previous steps. Apply the filter in those columns and in the Status column, filter the data using only the Unique

Remove Duplicates Based on Rows in Excel

  • Click OK to remove those duplicate rows. And our final result is here.

Remove Duplicates Based on Rows in Excel


4. Use VBA Codes to Remove Duplicates Based on Criteria

You can also use a VBA code to remove duplicates from the dataset. Let’s learn this method!

Step 1:

  • Go to a net worksheet. We will use the previous dataset. Press “CTRL+F11” to open the VBA window

Create a VBA code to Remove Duplicates Based on Criteria

Step 2:

  • In the new VBA window, select Insert and click on Module

Create a VBA code to Remove Duplicates Based on Criteria

  • Now write down the VBA We have written the code for you. You can just copy-paste the code into your VBA window. The code is,
Sub Delete_duplicate_rows()

Dim Rng As Range

Set Rng = Selection

Rng.RemoveDuplicates Columns:=Array(1), Header:=xlYes

End Sub

Create a VBA code to Remove Duplicates Based on Criteria

Step 3:

  • Close the VBA Now we will apply the code. To do that, select the range of cells that you want to deal with (B3:E13).
  • Select the Developer tab and click on the Macros option

Create a VBA code to Remove Duplicates Based on Criteria

  • This will open the Macro window where our written code is present. Now click on Run to apply the code.

Create a VBA code to Remove Duplicates Based on Criteria

  • And our duplicate values are successfully removed.

Create a VBA code to Remove Duplicates Based on Criteria


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.


Conclusion

In this article, we have shown four simple yet effective methods to remove duplicates based on criteria. We 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