How to Remove Duplicates But Keep Rest of the Row Values in Excel?

Duplicate data can clog your spreadsheet and make it difficult to accurately assess or present information. But fear not, Excel offers strong features to assist you in removing duplicates while keeping the crucial data in your dataset. Learning how to use these techniques can improve your data management abilities regardless of whether you are working with a little or huge dataset.

Remove Duplicates But Keep Rest of the Row Values

Here we will first create a VBA module and then run the code to complete the task. So let us see a simple process to know how you can remove duplicates but keep the rest of the row values in Excel.

Step 1

Consider an Excel sheet where you have a list of duplicate values.

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

Right-click > View Code.

Step 2

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

Insert > Module > Copy.

Code

Sub RemoveDuplicates()
   Dim xRow As Long
   Dim xCol As Long
   Dim xrg As Range
   Dim xl As Long
   On Error Resume Next
   Set xrg = Application.InputBox("Select a range:", "Remove Duplicate Keep", _
                                    ActiveWindow.RangeSelection.AddressLocal, , , , , 8)
   xRow = xrg.Rows.Count + xrg.Row - 1
   xCol = xrg.Column
   'MsgBox xRow & ":" & xCol
   Application.ScreenUpdating = False
   For xl = xRow To 2 Step -1
      If Cells(xl, xCol) = Cells(xl - 1, xCol) Then
         Cells(xl, xCol) = ""
      End If
   Next xl
   Application.ScreenUpdating = True
End Sub

Step 3

Then click F5 to run the module. Then select the range of cells and click OK to complete the task.

F5 > Select Cells > Ok.

This is how you can remove duplicates but keep the rest of the row values in Excel.

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can remove duplicates but keep the rest of the row values in Excel to highlight a particular set of data.