Compare 2 lists adding missing data from 2nd list to bottom of 1st list.

yegarboy · Jul 11th 2013

Good day Ladies and Gents,

I have 2 lists on 2 sheets. Each list is thousands of rows long.

What I need to do is loop through each Name in List2 Sheet2. And look for it in List1 Sheet1.

If a match is found then do nothing "Goto Next Cell" if Not..Then add it to the bottom of List1.

I just can't seem to wrap my head around this.

cytop · Jul 12th 2013

Re: Compare 2 lists adding missing data from 2nd list to bottom of 1st list.

Sledgehammer approach... there's probably more elegant ways to do this but it sounds like a one off...

Code

Option Explicit


Sub x()


    Dim r1 As Excel.Range
    Dim r2 As Excel.Range
    Dim r3 As Excel.Range
    Dim c As Excel.Range
    
    Dim x As Date
    
    Set r1 = Range("A1").Resize(Range("A" & Rows.Count).End(xlUp).Row)
    Set r2 = Range("B1").Resize(Range("B" & Rows.Count).End(xlUp).Row)
    
    x = Now
    
    For Each c In r2
        
        Set r3 = r1.Find(What:=c.Value, MatchCase:=False, Lookat:=xlWhole)
        
        If r3 Is Nothing Then
            
            Range("A" & Rows.Count).End(xlUp).Offset(1).Value = c.Value
            
        End If
    Next
    
    Debug.Print DateDiff("s", x, Now)
    
End Sub

Display More

Made up two lists with nearly 1,500,000 items with 15 missing from list1 - it took nearly a minute to run but I think my list is bigger than yours, and as mentioned, it sounds like a one-off

yegarboy · Jul 12th 2013

Re: Compare 2 lists adding missing data from 2nd list to bottom of 1st list.

Thanks Cytop, I will give it a whirl

Compare 2 lists adding missing data from 2nd list to bottom of 1st list.

Participate now!

Share