Table of Contents
The process of removing duplicates in Excel while retaining the row with the highest value involves identifying and eliminating duplicate entries in a spreadsheet, while ensuring that the row with the highest value is preserved. This can be achieved by using the “Remove Duplicates” function and selecting the column with the highest value as the key column. This will automatically remove duplicate entries while keeping the row with the highest value intact. This method is useful for organizing and streamlining data in Excel sheets, ensuring accuracy and efficiency in data management.
Excel: Remove Duplicates but Keep Row with Max Value
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 following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I remove duplicates in Excel while keeping the row with the highest value?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-remove-duplicates-in-excel-while-keeping-the-row-with-the-highest-value/
stats writer. "How can I remove duplicates in Excel while keeping the row with the highest value?." PSYCHOLOGICAL SCALES, 22 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-remove-duplicates-in-excel-while-keeping-the-row-with-the-highest-value/.
stats writer. "How can I remove duplicates in Excel while keeping the row with the highest value?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-remove-duplicates-in-excel-while-keeping-the-row-with-the-highest-value/.
stats writer (2024) 'How can I remove duplicates in Excel while keeping the row with the highest value?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-remove-duplicates-in-excel-while-keeping-the-row-with-the-highest-value/.
[1] stats writer, "How can I remove duplicates in Excel while keeping the row with the highest value?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I remove duplicates in Excel while keeping the row with the highest value?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.