PDA

View Full Version : [SOLVED] Deleting all subsequent duplicates below first one in sorted list.



slang
03-06-2015, 08:41 AM
I have a datasheet that has somehow had duplicates introduced into it. there is no time / date stamp to indicate which one is the newest but from the data the newest one always appears first in the list from top to bottom.
I was thinking about a simple script that would start at the bottom of the list and check if there were dups above it and if so delete the row them offset to the next row and do it all over again until the top of the list.


Question, how do I answer the question is there a duplicate in the list above to throw the switch to delete as the reference to the list will change as rows are deleted.

data is in column A

Haven't been coding for a while so am a bit rusty:help
Using Excel 2010

mancubus
03-06-2015, 08:54 AM
test with a backup file;

assuming A1 houses the column header...



Sub del_dupes_col_A()

Dim LastRow As Long, i As Long

With Sheets("Sheet1") 'change sheet name to suit
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
For i = LastRow To 2 Step -1
If Application.CountIf(.Range("A2:A" & LastRow), .Range("A" & i)) > 1 Then .Rows(i).Delete
Next i
End With

End Sub

mancubus
03-06-2015, 08:57 AM
for the second part, though i'm not sure i understand correctly, you can try below code. but it's not necessary.




Sub del_dupes_col_A()

Dim LastRow As Long, i As Long

With Sheets("Sheet1") 'change sheet name to sit
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
For i = LastRow To 2 Step -1
If Application.CountIf(.Range("A2:A" & LastRow), .Range("A" & i)) > 1 Then
.Rows(i).Delete
LastRow = LastRow - 1
End If
Next i
End With

End Sub

slang
03-06-2015, 10:32 AM
omg, it has been a while for me, duh:doh:, countif function.
Sometimes the simplest solution is the best. It's Friday.
Thanks very much and have a great weekend.:thumb