Cut and paste duplicate data

[Dileep Chandran]

Hello,

I have a workbook which contains name, street address, city and state
in columns C to F and date in column G.
Can anybody help me in getting a macro which does the following:

1. Find duplicate, if the entire data (Column C to Column F) is
repeating.
2. Cut and Paste the entire row of duplicates into a new sheet, except
the row which has the latest date in Column G.

Is my question clear?

Any help is greatly appreciated.

-Dileep

 

[Dileep Chandran]

A small addition. The range to look up for duplicate is C2:F1000.

 

[MrScience]

This would handle the deletion of any rows where the Name, Address,
City and State are exactly the same as long as the sheet has been
sorted. I noticed you've received information on how to handle the
sort in a previous post so I didn't include it here.

The cut and paste is pretty involved. Actully, the process of finding
the duplicate and then preparing to paste is pretty simple but you
always have to reference the last used cell in the sheet where you're
going to paste the duplicate.

This code assumes the data has been sorted and then are no blank rows.
As long as that is true, there is no need to worry about the range
reference for the last cell as the code will stop running when it hits
the last row based on the Do loop.

Sub deleteDuplicates()

Dim myName As Variant 'Name column
Set myName = Range("C2")

Do While Not IsEmpty(myName)

Set myAddress = myName.Offset(0, 1)
Set myCity = myName.Offset(0, 2)
Set myState = myName.Offset(0, 3)

Set nextName = myName.Offset(1, 0)
Set nextAddress = myAddress.Offset(1, 0)
Set nextCity = myCity.Offset(1, 0)
Set nextState = myState.Offset(1, 0)

If myName = nextName And _
myAddress = nextAddress And _
myCity = nextCity And _
myState = nextState Then
myName.EntireRow.Delete
End If

Set myName = nextName

Loop


End Sub

 

[Dileep Chandran]

Thank you Mr. Science. But we have to add some code to this.
Instead of deleting the entire row (myName.EntireRow.Delete), we have
to look up Column H for A or B. If there is only one A, then delete the
other column. If both are A, look up Column G and delete the oldest
data (we have to check the latest date).

Thanks in advance for any addition.

-Dileep