Deleting rows by specified criteria for duplicates

[Tasha]

I have gone through all of the posts looking for an answer to my problem, and
haven't been able to find one yet. Hoping someone can help me???

I have a spreadsheet that contains the following rows:
A-Date of Operation
B-Acct#
C-Type
D-Name
E-Doctor
F-Operation

I have many duplicates. I need to delete only 1 row of the duplicates if
they match in columns A-B and E.

Can someone help me with this? This is a huge spreadsheet and is taking
forever to do it manually. Thanks!!

 

[Bob Phillips]

Sub DeleteData()
Dim LastRow As Long
Dim rng As Range

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Columns("F").Insert
.Range("F2").Resize(LastRow - 1).Formula = _
"=SUMPRODUCT(--($A$2:$A2=A2),--($B$2:$B2=B2),--($E$2:$E2=E2))>1"
.Range("F1").Value = "temp"
.Columns("F").AutoFilter field:=1, Criteria1:="TRUE"
Set rng = .Rows(2).Resize(LastRow -
1).SpecialCells(xlCellTypeVisible)
If Not rng Is Nothing Then rng.Delete
.Columns("F").Delete
End With
End Sub

 

[Tasha]

Thanks Bob. I tried it though, and it deleted everything except the
column headers???? Did I do something wrong?

 

[Sheeloo]

One way is to do the following ;

Assuming first row is a header row and you have 1,000 records
Then in G2 enter =A2&B2&E2
Copy down to G1000

In H2 enter =IF(COUNTIF(G2:$G$1000,A1)>1,"Duplicate","Unique")
Copy down to G1000

Filter H on "Duplicate" and delete the selected rows.

(DO make a copy before trying this out)

 

[Bob Phillips]

It shouldn't, didn't in my tests.

Can you layout some sample data for me to test it with?