How to Remove Duplicate Rows and Keep Highest Values Only in Excel?

Millions of users rely on the robust spreadsheet programme Excel for a variety of data management activities. Dealing with duplicate rows in datasets is a regular issue for Excel users. Data entry mistakes, the import of data from different sources, or the merging of datasets can all result in duplicate rows.

We'll walk you through the steps in this tutorial for deleting duplicate rows from your Excel spreadsheet while keeping the highest values. This ensures that your data is correct and well-organized by saying that when duplicates are present, we will only maintain the row with the greatest value in the relevant column.

Remove Duplicate Rows and Keep Highest Values Only

Here, we will first create a VBA module and then run it to complete the task. So let us see a simple process to know how you can remove duplicate rows and keep the highest values only in Excel.

Step 1

Consider an Excel sheet where you have duplicate rows with different values in one column, similar to the below image.

First, right-click on the sheet name and select View code to open the VBA application.

Step 2

Then click on Insert and select Module, then copy the below code into the text box.

Insert > Module > Copy.

Code

Public Sub DelDupes() Dim xFilterRg As Range Dim xCriteriaRg As Range Dim xAddress As String Dim xSUpdate As Boolean On Error Resume Next xAddress = Application.ActiveWindow.RangeSelection.Address Set xFilterRg = Application.InputBox("Please select a range:", "Remove Duplicates Highest", xAddress, , , , , 8) If xFilterRg Is Nothing Then Exit Sub Set xFilterRg = Application.Intersect(ActiveSheet.UsedRange, xFilterRg) If xFilterRg Is Nothing Then Exit Sub On Error GoTo 0 xSUpdate = Application.ScreenUpdating Application.ScreenUpdating = False Set xCriteriaRg = ActiveSheet.Cells(1, xFilterRg.Column + xFilterRg.Columns.Count).Resize(2) xCriteriaRg.Resize(1).Offset(1).Formula = "=COUNTIF(" & xFilterRg(2, 1).Address & ":" & _ xFilterRg(2, 1).Address(False, False) & "," & _ xFilterRg(2, 1).Address(False, False) & ")>1" With xFilterRg .Sort key1:=xFilterRg.Cells(1, 1).Offset(0, 1), order1:=xlDescending, Header:=xlYes .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=xCriteriaRg .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete .Parent.ShowAllData End With xCriteriaRg.Clear Application.ScreenUpdating = xSUpdate End Sub 

Step 3

Then click F5 to run the module. Then select the column where you have values to remove duplicates based on and click OK.

This is how you can remove duplicate rows and keep the highest values in Excel.

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can remove duplicate rows and keep the highest values only in Excel to highlight a particular set of data.