PDA

View Full Version : [SOLVED] Look for duplicates across 2 identical tabs, remove then merge remaining data



mykal66
05-10-2017, 01:12 AM
Hi
I have found several pieces of code on the internet but cannot adapt any of them to do what I need so looking for help if anyone can please?
I have a report at work which I have to update daily with new data but I need to be able to drop the updated report into a new tab then use macro to check if the data already exists in original tab, if delete the entire line so only new data remains. Then I need it to copy the new data to the bottom of the existing data tab.
In the attached example, DATA is the constant report and ADDITIONAL where I would drop new data. There are several lines of header infor, summaries etc so it would check Columns D in both tabs from row 12 and look for duplicates, so anything identified in Additional as already existing in DATA would have the entire row deleted to leave only new rows to pull over to the data tab – just the 3 rows highlighted in green in example.
These rows would then be automatically added to the bottom of the DATA tab giving one dataset without any duplicates.
As always, your help is always appreciated
Mykal
19117

mdmackillop
05-10-2017, 04:56 AM
Option Explicit


Sub Test()
Dim Source As Range, cel As Range, c As Range
Dim Target As Range

With Sheets("Update")
Set Source = Range(.Cells(13, 4), .Cells(Rows.Count, 4).End(xlUp))
End With
With Sheets("Data")
Set Target = Range(.Cells(13, 4), .Cells(Rows.Count, 4).End(xlUp))
End With

For Each cel In Source
Set c = Target.Find(cel.Value, lookat:=xlWhole)
If c Is Nothing Then
cel.EntireRow.Copy Sheets("Data").Cells(Rows.Count, 1).End(xlUp)(2)
End If
Next cel
End Sub

mykal66
05-10-2017, 05:16 AM
Hi and thank you very much for coming back so quickly, it look far less complicated than some of the code i had tried to figures out.

I've attached the code you provided to the command button but when i run it i get a run time error and this line highlights
Set Target = Range(.Cells(13, 4), .Cells(Rows.Count, 4).End(xlUp))

Mykal

mdmackillop
05-10-2017, 05:31 AM
Put the code in a standard module and call it from the button.

mykal66
05-10-2017, 07:51 AM
Hi and thank you again.

Just tried on my test sheet and works like a charm, I'll put it into real thing tomorrow.

Brilliant thank you.

Mykal