Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cut and paste duplicate data
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cut and paste duplicate data
A small addition. The range to look up for duplicate is C2:F1000. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cut and paste duplicate data
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cut and paste duplicate data
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
EXCEL PASTE DOES NOT CHANGE DATA - PASTE DOESN'T WORK! | Excel Worksheet Functions | |||
Macro - Cut and paste a row if duplicate | Excel Programming | |||
Excel cut/Paste Problem: Year changes after data is copy and paste | Excel Discussion (Misc queries) | |||
how can i locate duplicate data in an excel data table? | Excel Worksheet Functions | |||
Scanning for Duplicate Data in a Column, Merging Data and Finally Removing All Duplicates...? | Excel Programming |