I have the following problem: I have two lists, A & B. list B updates regularly and may include new values. List A stays static. How do I go about appending new items in list B, that are not currently in list A, to list A?
I could probably do a vlookup and return missing values but then I don't know how to append those missing items to list A.
Struggling to understand if theres a simple VBA code to perform this operation?
So using the Answer below, I attempted to script a macro, but I am getting a runtime error saying object not defined when I attempt to Add items to the Dictionary?:
Option Explicit Sub AppendProfitCentres() Dim LastRowRecon As Long Dim LastRowSAP As Long Dim Dict As Object Dim MissingPC As Long Dim i As Integer Worksheets("Recon").Range("K6").Select Worksheets("Recon").Range("K6", Selection.End(xlDown)).Select LastRowRecon = Cells(Rows.Count, 11).End(xlUp).Row Cells(LastRowRecon, 11).Select ' ''create dictionary to hold profit centres ' ' Set Dict = CreateObject("Scripting.Dictionary") Worksheets("Recon").Range("K6").Select For i = 6 To LastRowRecon ' Dict.Add Key:=Worksheets("Recon").Range(i, 11).Value, Item:=vbNullString Next i 'check SAP and TCM profit centres against Dictionary PC Worksheets("SAP Data").Range("A7").Select Worksheets("SAP Data").Range("A7", Selection.End(xlDown)).Select LastRowSAP = Cells(Rows.Count, 1).End(xlUp).Row For i = 7 To LastRowSAP If Not PC.Exists(Worksheets("SAP Data").Range(i, 1).Value) Then 'if item doesnt exist, append to profit centres in recon tab MissingPC = Empty MissingPC = Worksheets("SAP Data").Range(i, 1).Value Cells(LastRowRecon, 11).Select ActiveCell.Offset(1).EntireRow.Insert ActiveCell.Value = MissingPC End If Next i End Sub