Often you may want to remove duplicate rows in Excel but keep the row that contains the max value in a particular column.
For example, suppose you have the following dataset that contains information about various basketball players and you’d like to remove rows with duplicate values in the Team column but keep the row with the max value in the Points column:
The following example shows exactly how to do so.
Example: Remove Duplicates but Keep Row with Max Value in Excel
First, type the following formula into cell D2:
=UNIQUE(A2:A10)
This produces a list of unique team names from the dataset:
Next, type the following formula into cell E2 to find the max value in the points column associated with each unique team name:
=MAX(IF($A$2:$A$10=D2,$B$2:$B$10))
The following screenshot shows how to use this formula in practice:
Columns D and E now show the original dataset with the rows removed that had duplicate values in the team column and only kept the rows with the max value in the points column.
For example, there are three rows with a duplicate “Mavs” value in the team column but the row with the max points value among these three rows is the one with 28 points:
This is the one row that is kept among the “Mavs” rows in the filtered dataset.
The same process is repeated for the Spurs and the Rockets.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Count Duplicates
Excel: How to Find Duplicates Using VLOOKUP
Excel: How to Remove Duplicate Rows Based on Two Columns
