Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
144 Views
