Removing duplicate cells/rows in excel

  • Thread starter Thread starter Joe90
  • Start date Start date
J

Joe90

I have a spreadsheet with 1,000 rows.
Each row has a persons name (e.g. James J Smith) as the
first cell.
There are multiple entries for a single person (e.g. James
J Smith). The first entry found is the one with the latest
data.

I need to remove all the duplicate rows for a single person
so that I end up with one entry per person that is the
first one found in the spreadsheet.
What is the best way of doing this?
 
Hi Joe,

A macro would probably be the most efficient way, though would could do it
manually using an oldest>newest sort and conditional formatting to highlight
duplicates and deleting highlighted rows from oldest to newest, one at a
time. Done properly, the highlighting would disappear from the remaining
rows as their duplicates are deleted.

Here's a macro solution:

Sub DeleteDuplicateRows()
Dim lLastRow As Long, lLastCol As Long
Dim I As Long, J As Long, K As Long
lLastRow = ActiveSheet.UsedRange.Rows.Count - 1
lLastCol = Selection.Columns.Count - 1
For I = 0 To lLastRow - 1
For J = lLastRow To I + 1 Step -1
For K = 0 To lLastCol
If ActiveSheet.Range("A1").Offset(I, K).Value <> _
ActiveSheet.Range("A1").Offset(J, K).Value Then
Exit For
End If
Next K
If K > lLastCol Then
ActiveSheet.Range("A1").Offset(J, 0).EntireRow.Delete
'Else EntireRow.Hidden = False
End If
Next J
Next I
End Sub

Note: this macro is case sensitive and requires you to select the column(s)
on which the duplicates comparison is to be done.

Cheers
 
Back
Top