How to Compare Two Lists in VBA (Excel Macro)

Most VBA "compare two columns" macros people copy-paste online use a nested loop, which works but slows to a crawl on large ranges. Here's the fast approach using Scripting.Dictionary, plus two simpler alternatives for smaller lists, in order of how often you'll actually reach for each.

Scripting.Dictionary: the fast approach for large ranges

Sub CompareTwoLists()
    Dim dict As Object
    Dim arrA As Variant, arrB As Variant
    Dim i As Long, outputRow As Long
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    ' Late binding - no References needed
    Set dict = CreateObject("Scripting.Dictionary")

    ' Read both ranges into memory in one shot - avoids slow cell-by-cell access
    arrA = ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row).Value
    arrB = ws.Range("B2:B" & ws.Cells(ws.Rows.Count, "B").End(xlUp).Row).Value

    ' Load list B into the dictionary as keys
    For i = LBound(arrB, 1) To UBound(arrB, 1)
        If Not dict.Exists(arrB(i, 1)) Then dict.Add arrB(i, 1), True
    Next i

    ' Walk list A, write out anything not found in B
    outputRow = 1
    For i = LBound(arrA, 1) To UBound(arrA, 1)
        If Not dict.Exists(arrA(i, 1)) Then
            ws.Cells(outputRow, "D") = arrA(i, 1)
            outputRow = outputRow + 1
        End If
    Next i
End Sub

Reading both ranges into Variant arrays first, then looping in memory, avoids the single biggest slowdown in VBA macros - crossing from VBA into Excel's calculation engine on every cell access. The Dictionary lookup itself is O(1) per check, so the whole macro runs in roughly O(n + m) instead of O(n * m) for a nested-loop version.

Early binding for faster Dictionary calls

' Tools > References > Microsoft Scripting Runtime
Dim dict As Scripting.Dictionary
Set dict = New Scripting.Dictionary

Late binding (CreateObject) needs no reference and runs anywhere, but early binding gives you IntelliSense and is marginally faster since VBA doesn't have to resolve method calls at runtime. For a macro you're distributing to other machines, late binding is safer since you can't guarantee the recipient has the same reference checked.

CountIf: simpler, fine for smaller lists

Sub CompareWithCountIf()
    Dim cell As Range
    Dim rangeB As Range
    Set rangeB = ThisWorkbook.Sheets("Sheet1").Range("B2:B500")

    For Each cell In ThisWorkbook.Sheets("Sheet1").Range("A2:A500")
        If WorksheetFunction.CountIf(rangeB, cell.Value) = 0 Then
            Debug.Print cell.Value & " is only in list A"
        End If
    Next cell
End Sub

No Dictionary, no array reading - just a direct worksheet function call per cell. It's easy to read and fine for a few hundred rows, but CountIf re-scans the entire rangeB for every single cell checked, so it becomes noticeably slow well before the Dictionary version would.

Case sensitivity and the Option Compare directive

Option Compare Text   ' place at the very top of the module, above any Sub

VBA string comparisons default to Option Compare Binary (case-sensitive) unless a module says otherwise. Option Compare Text switches string comparisons - including = and Dictionary key lookups on string keys - to case-insensitive for the whole module. It must be the very first line of the module, before any procedure declarations.