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.

Pradeep Kumar

e

Updated on: 07-Sep-2023

102 Views

Kickstart Your Career

Get certified by completing the course

Get Started