Compare 2 col listings n append items in A not in B to bottom of B
 

In Sheet1 (the codename)
In B19 down is a list of unique items:
xx
yy
zz

In Sheet2 (the codename)
In A3 down is another list of unique items:
aa
yy
bb

I need a sub to compare the 1st list with the 2nd, and to append any items
in the 1st which are not found in the 2nd list, to the bottom of the 2nd
list. Example, for the sample data above, the 2nd list should finally look
like this:
aa
yy
bb
xx
zz

(xx, zz are appended)

The sub is to reference the 2 codenames
Thanks for insights

Compare 2 col listings n append items in A not in B to bottom of B
 

Hi Max,

Try the following.

Sub AppendMissingData()

Dim rng1 As Range
Dim rng2 As Range
Dim cel As Range
Dim foundCel As Range

With Sheet1
Set rng1 = .Range("B19", _
.Cells(.Rows.Count, "B").End(xlUp))
End With

With Sheet2
Set rng2 = .Range("A3", _
.Cells(.Rows.Count, "A").End(xlUp))
End With

For Each cel In rng1
Set foundCel = rng2 _
.Find(What:=cel.Value, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)

If foundCel Is Nothing Then

Sheet2.Cells(Sheet2.Rows.Count, "A") _
.End(xlUp).Offset(1, 0) = cel.Value

End If

Next cel
End Sub

--
Regards,

OssieMac

Compare 2 col listings n append items in A not in B to bottom
 

That was marvellous, OssieMac !
Many thanks for your sub

Compare 2 col listings n append items in A not in B to bottom
 

Thanks for the feed back Max. However, I have realized that if the range in
sheet1 had multiple instances of 'new' values that are the same, it will
append them multiple times in sheet2. If there will never be multiple
instances of the 'new' data then no problem but if there can be then replace
the sheet2 range code with the following and it will always test against the
updated list including any appended data and only append one instance of
missing data.

With Sheet2
Set rng2 = .Range("A3", _
.Cells(.Rows.Count, "A"))
End With

--
Regards,

OssieMac


"Max" wrote:

That was marvellous, OssieMac !
Many thanks for your sub

Compare 2 col listings n append items in A not in B to bottom
 

Noted with thanks, OssieMac.
It should not happen (dups in Sheet1) but you never know