delete duplicate entries

F

facams

New member
Joined
Nov 17, 2017
Messages
4
Reaction score
0
Points
0
HI, new here. I need help in deleting duplicate cells that have same name or account # then based on an action that states "report" even if the others don't I need them deleted:

Name Acct #action
jon Doe123456789report
Mary Smith12345call
jane grant987654wait
jon Doe123456789mail
jon Doe123456789call


so I need jon doe to be deleted not only from the line that says report, but also the other 2 entries with his name.

I am stumped


thank you
 
Code: 
Public Sub DeleteDuplicates()
Dim lastrow As Long
Dim i As Long
    Application.ScreenUpdating = False
    
    With ActiveSheet
    
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        
        For i = 2 To lastrow
        
            If Application.CountIfs(Columns(1), .Cells(i, "A").Value, Columns(2), .Cells(i, "B").Value) > 1 Then
            
                .Cells(i, "D").Value = True
            End If
        Next i
        
        For i = lastrow To 2 Step -1
        
            If .Cells(i, "D").Value Then .Rows(i).Delete
        Next i
    End With
    
    Application.ScreenUpdating = True
End Sub
 
I tried to run it but it deletes the wrong cells and leaves those with report still listed? I cant figure out what I am doing wrong. thanks in advance
 
This is a well-known and documented problem. Have you searched the Net?
 
Works fine for me, against the data you gave in your example.
 
You must log in or register to reply here.
Back
Top