Dup check 2 columns - need it to delete correct one

  • Thread starter Thread starter Chance M
  • Start date Start date
C

Chance M

Using Excel 2007, I have a two column list with over 100,000 rows.

Example:

Jane Doe U
Jane Doe
John Doe
John Doe U
John Doe
Jack Doe
Jack Doe

I want to run a dup check to make sure there is only one Jane, Jack or John
Doe, but the copy I want to stay is the one with a U behind it if there is
one (wouldn't matter which row is kept for Jack). From what I've done so far,
the dup check simply keeps whichever record is in the top row. Seems like it
should be an easy fix, but I'm lost. Please help.
 
Hi,
If the rows you want to keep are the ones with a U, apply a filter to that
column, uncheck the U, then higlight all the filtered rows, right click on
the mouse, delete, then go to the filter and check the U
 
Thanks Eduardo. I still need to keep one copy of any that don't have a U in
column B by them as well. So in the below example I would be left with:

Jane Doe U
John Doe U
Jack Doe

Does that make sense?
 
I'm having trouble fixing this to be right in my situation... Any thoughts?
You've been so helpful today!

Sub DeleteTheOldies()
Dim RowNdx As Long
For RowNdx = Range("A1").End(xlDown).Row To 2 Step -1
If Cells(RowNdx, "A").Value = Cells(RowNdx - 1, "A").Value Then
If Cells(RowNdx, "B").Value <= Cells(RowNdx - 1, "B").Value Then
Rows(RowNdx).Delete
Else
Rows(RowNdx - 1).Delete
End If
End If
Next RowNdx
End Sub
 
Back
Top