compare list a & b and add records of b not in a to a

[ImplodedBrain]

HI,
I suspect this is yet another VLOOKUP type question, but after much fandangling I can't make it work.

The only complicating factor is that the lists are on different worksheets

 

[Jay Petrulis]

This routine has your master list on Sheet1, Column A and the list to run through on Sheet2, Column A

It will run through each cell on Sheet2 and compare it to the list on Sheet1. Only those missing from the Sheet 1 list will be added. It resets the master list after each pass so that any duplicate missing items are added only once.

Sub test()
Dim Rng1 As Range, Rng2 As Range, C As Range
Dim Z

With Sheets("Sheet2")
    .UsedRange
    Set Rng2 = Intersect(.UsedRange, .Range("A:A"))
    For Each C In Rng2
        With Sheets("Sheet1")
        .UsedRange
            Set Rng1 = Intersect(.UsedRange, .Range("A:A"))
            Z = Application.Match(C, Rng1, 0)
            If WorksheetFunction.IsError(Z) Then
                .Cells(.Cells(65536, 1).End(xlUp).Row + 1, 1) = C
            End If
        End With
    Next C
End With
MsgBox "Done!"

End Sub

HTH,
Jay

 

[Chris Davison]

Another option, probably more time-consuming though in the long run, is advanced filter :

copy list B so it sits directly underneath list A

highlight the range
data
filter
advanced filter
action = copy to another location
list range = should already be highlighted
copy to = the cell you want to put them
unique value = checked
hit "ok"

Admittedly, this gives you a list of unique values........ so if you original A list or you B list themselves contained duplicates which you wanted retaining, this won't work