Often you may want to remove both duplicate and original values in a dataset in Excel.
For example, suppose you have the following dataset that contains information about various basketball players:
Suppose you’d like to remove both duplicate and original values in the Team column for any team that does happen to have duplicate values.
The following example shows exactly how to do so.
Example: Remove Both Duplicate and Original Values in Excel
First, we need to identify the rows with team names that occur more than once.
To do so, we can type the following formula into cell D2:
=COUNTIF($A$2:$A11,$A2)>1
The following screenshot shows how to use this formula in practice:
Next, type the following formula into cell F2 to filter the dataset so that all rows with team names that occur more than once are removed:
=FILTER(A2:C11, D2:D11=FALSE)
The following screenshot shows how to use this formula in practice:
Noticed that the filtered dataset only contains the rows for the teams that occur exactly once.
For example, the following teams have all been filtered out:
- Mavs (occurs twice in original dataset)
- Rockets (occurs twice in original dataset)
- Nets (occurs three times in original dataset)
Note that we used the FILTER function to filter columns A through C for rows where the corresponding value in column D was FALSE.
This allowed us to remove both duplicate and original values in the Team column for any team that did happen to have duplicate values.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Count Duplicates
Excel: How to Highlight Duplicate Rows
Excel: How to Find Duplicates Using VLOOKUP
Excel: How to Remove Duplicate Rows Based on Two Columns
