VBA Dictionary Object: Deduplicate a 10,000-Row AP Ledger

This guide is part of the FinDataPro Excel VBA Workbook Events series.

The Dictionary Deduplication Pattern

Run this against an AP ledger with a vendor name column:

Option Explicit

Sub DeduplicateVendorNames()
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("APLedger")

    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    Dim i As Long, vendorKey As String
    For i = 2 To lastRow
        vendorKey = UCase(Trim(ws.Cells(i, "A").Value))
        If Not dict.Exists(vendorKey) Then
            dict.Add vendorKey, ws.Cells(i, "A").Value
        End If
    Next i

    MsgBox dict.Count & " unique vendors found from " & (lastRow - 1) & " rows.", vbInformation
End Sub

No reference needed — CreateObject("Scripting.Dictionary") late-binds the object at runtime.

Where the Code Goes

This lives in a standard module. It pairs naturally with Loop Through Files in a Folder when the ledger being deduplicated is itself assembled from multiple imported statements or extracts first.

Finance Use Case: A 10,000-Row AP Ledger

A vendor master list built up over years of manual entry accumulates near-duplicates: Acme Supplies, ACME SUPPLIES, Acme Supplies with an extra space. On 10,000 rows, spotting these by eye is not realistic, and a nested-loop comparison is slow enough to make the macro feel broken. A Dictionary keyed on a normalised (trimmed, upper-cased) version of the name collapses all three into one entry in a single pass.

This matters beyond tidiness. A vendor treated as three separate entities in reporting understates that vendor's real spend across every analysis that groups by name — ageing, concentration risk, year-over-year trend — each silently splitting one relationship into fragments too small to trigger the thresholds that would otherwise flag it. Deduplicating before any of that analysis runs is what makes the downstream numbers trustworthy in the first place, rather than a cosmetic cleanup step done after the fact.

Why Not Just a Nested Loop

A nested loop comparing every row against every other row costs roughly n² comparisons — on 10,000 rows, that is up to 100 million. A Dictionary's .Exists check is a near-instant hash lookup regardless of how many keys it already holds, so the whole deduplication pass stays close to linear time — the difference between a macro that finishes instantly and one that visibly hangs.

Common Mistakes

• Calling dict.Add without checking .Exists first, which raises a runtime error on the first genuine duplicate.
• Forgetting that keys are case-sensitive by default — UCase or LCase the key before comparing if casing varies in the source data.
• Not trimming whitespace, so "Acme" and "Acme " are treated as different vendors.
• Assuming early binding (adding a Microsoft Scripting Runtime reference) is required — it is optional and only adds IntelliSense, not functionality.

Related Guides on This Series

Pair this with Array vs Range Performance for the same large-dataset speed mindset, or On Error GoTo to handle a source column that isn't consistently populated.

Full reference: Microsoft Learn Dictionary object reference.

Frequently Asked Questions

Conclusion

A single Scripting.Dictionary pass replaces a nested loop that would visibly hang on a real AP ledger. Normalise the key before adding it, and pair this pattern with Array vs Range Performance any time a macro needs to stay fast on a genuinely large ledger. If the AR side of vendor and customer hygiene is the real bottleneck, see AR Intelligence Manager.

Prashant Panchal• ACA | FMVA® | 19 Years in Finance

Prashant Panchal is a Chartered Accountant (ACA) and Financial Modelling & Valuation Analyst (FMVA®) with 19 years of experience in finance, FP&A, and financial modelling across the GCC region. He is the founder of FinDataPro.