Deleting rows by specified criteria for duplicates
 

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!!

Deleting rows by specified criteria for duplicates
 

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



--
__________________________________
HTH

Bob

"Tasha" wrote in message
...
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!!

Deleting rows by specified criteria for duplicates
 

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

"Bob Phillips" wrote:

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



--
__________________________________
HTH

Bob

"Tasha" wrote in message
...
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!!

Deleting rows by specified criteria for duplicates
 

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)

"Tasha" wrote:

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!!

Deleting rows by specified criteria for duplicates
 

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

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

--
__________________________________
HTH

Bob

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

"Bob Phillips" wrote:

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



--
__________________________________
HTH

Bob

"Tasha" wrote in message
...
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!!