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?

*****UPDATE:******

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

1 Answers

0
Community On

I am a big fan of Dictionaries for situations like this. Taking advantage of their unique keys and the Exists method make life a lot easier than constantly looping for each item you would like to check. Just make sure you add the reference to your project so you can access the Scripting.Dictionary from Windows.

The code below is like a framework that will get you heading in the right direction. I did make some assumptions that I tried to point out in the code like list A is column A and I didn't account for headers.

Option Explicit

Public Sub CreateDictToCompare()

    Dim LastRow As Long
    Dim i As Long
    Dim Dict As Scripting.Dictionary

    'Get's the last row of column A
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With

    Set Dict = New Scripting.Dictionary

    For i = 1 To LastRow
        'Assuming List A is unique values and in Column A
        Dict.Add Key:=ActiveSheet.Range(i, 1).value, Item:=vbNullString
    Next i

    'Gets the last row of column B
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, 2).End(xlUp).Row
    End With

    For i = 1 To LastRow
            'Assuming the Values you want to compare are in column B
        If Not Dict.Exists(ActiveSheet.Range(i, 2).value) Then
            'You will only get here if the Value is not in list A.
            'You can use this space to append this value to list B
        End If
    Next i

End Sub

Hopefully this gets you moving in the right direction and best of luck!