Every once in a while you need to go beyond the Excel feature that screens out duplicate records and single out records with a duplicate field, that contain data you want to get rid of.

Here’s an Excel VBA Macro that does that. This one works for a sheet called “singletons” so you’re going to have to change “singletons” to “sheet1” or whatever your sheet is called.

If Sheets(“singletons”).Cells(i,2) = Sheets(“singletons”).Cells(i-2,2) then

This also starts at row 100679, so you’ll need to change that to your last row. The program works from the bottom up, which is the way it has to be. So make sure that the row and data you want to keep is sorted so the good record is the first one, and all the bad records are below that. This Macro checks the data in the B or second column, so you’ll probably need to change that too:

Cells(i,2)

USE AT YOUR OWN RISK. BACK YOUR DATA UP!!!!

Here’s the whole macro:

Sub deleteRowsWithDuplicateData()

For i = 100679 To 2 Step -1
If Sheets(“singletons”).Cells(i, 2) = Sheets(“singletons”).Cells(i – 1, 2) Then

Rows(i).Select

Selection.Delete Shift:=xlUp

End If

Next

End Sub