Macro help - Merge 2 lists and discard duplicates

[diberlee]

Hi,

Hoping to get some help with an issue, everyone on this forum has been brilliant in the past.

I have been tasked with making a sheet to collate a list of customers we need to contact. The sheet needs to

1/ format the mixed up sheet that is generated by our system
2/ update regularly with new information
3/ periodically start from fresh

I have been able to write macros for 1 and 3 that work as needed, but 2 is a little beyond my knowledge. So far the best I've done is make a macro that deletes everything and then makes my screen flicker until I force shutdown the PC. That's where I'm hoping to get some help.

For some reason I can't upload a copy of the sheet... The sheet I am using has 2 pages. 1 page that lists all the current customers we need to get in touch with, and another where I paste the report generated by our systems and use a macro to format it in the same way as page 1. It has 9 columns, Column A contains a unique reference, and column H is the date that contact has been outstanding since. I would like to push a button on page 2 ("Raw") and copy any entries that are not already on page 1 ("Data") to the list.

Sub updateExisting()

Dim iListCount, iCtr As Integer

nresult = MsgBox( _
        Prompt:="This will add all new entries from the list below to the current report. Any unneccessary entries will be deleted from this page. Are you sure you want to do this?", _
        Buttons:=vbYesNo)
If nresult = vbNo Then
    MsgBox "No changes made."
    Exit Sub
End If
    Application.ScreenUpdating = False
  
    With Worksheets("Raw")
        rawCount = .Range("A" & .Rows.Count).End(xlUp).Row
    End With
    
    With Worksheets("Data")
        dataCount = .Range("A" & .Rows.Count).End(xlUp).Row
    End With
    

    iListCount = Sheets("Raw").Range("A2:A30002").Rows.Count

    For Each x In Sheets("Data").Range("A4:A30004")
       For iCtr = 1 To iListCount
          If x.Value = Sheets("Raw").Cells(iCtr, 1).Value Then
             Sheets("Raw").Row(iCtr, 1).Delete xlShiftUp
             iCtr = iCtr + 1
          End If
       Next iCtr
    Next

    
    Application.ScreenUpdating = True



End Sub

This is the what I have so far, most of what is in there is an example macro I found through googling, but it doesn't seem to work. The list that we use is a great deal larger than the dummy sample data I have been using - around 15,000 entries on average, so each run has to make something like 275,000,000 comparisons. I let it run for about 1 hour before I had to force shutdown. Since it is such a long list I am thinking the best approach would be to find any entries on page 2 where the date in column H is greater or equal to the most recent date on page 1 and copy them to the end of the list. Then have a bit of code that will scan through the list on page 1 to remove any duplicates. The problem is that I just can't think how to go about doing this. Maybe somebody else can think of a better way.

Can anybody help? I don't want you to write all the code for me, just some pointers in the right direction will do, unless it's a lot simpler than I'm making it of course... =)

Cheers
Duane