Counting 'ABC', 'abc', and 'ABC ' as the Same Duplicate in VBA Dictionary: Tips for Key Normalization

About This Article
This article was created using an automated generation workflow leveraging generative AI. Based on the official Microsoft Learn documentation for the Dictionary object, it focuses on the practical task of key normalization—specifically how to standardize leading/trailing spaces and letter casing—rather than just duplicate aggregation itself.

Verification Status: 📘 Confirmed with Microsoft official documentation, Excel physical execution unverified

In Excel duplicate checks, data entries that appear almost identical are sometimes counted as distinct items.

For example,

ABC
abc
ABC 

If you want to ignore trailing spaces or differences in letter casing, you should determine what constitutes a duplicate and normalize the keys before adding them to the Dictionary.

First, an Example of What Typically Fails

If you add data directly to the Dictionary, differences in values become differences in keys.

Sub CountRawKeys()

    Dim d As Object
    Dim v As Variant

    Set d = CreateObject("Scripting.Dictionary")

    For Each v In Array("ABC", "abc", "ABC ")

        If d.Exists(CStr(v)) Then
            d(CStr(v)) = d(CStr(v)) + 1
        Else
            d.Add CStr(v), 1
        End If

    Next v

    For Each v In d.Keys
        Debug.Print "[" & v & "]", d(v)
    Next v

End Sub

In this case, the entry with trailing spaces ABC is treated as a separate key.

How to Remove Leading and Trailing Spaces

To remove spaces consistently, consolidate key generation into a single location.

Private Function NormalizeKey(ByVal value As Variant) As String

    ' Trim leading and trailing spaces and convert to string
    NormalizeKey = Trim$(CStr(value))

End Function

On the aggregation side, process the data using the function before adding it to the Dictionary.

How to Treat Uppercase and Lowercase Letters as Identical

By setting CompareMode to vbTextCompare, you can treat uppercase and lowercase letters as identical keys.

Set d = CreateObject("Scripting.Dictionary")

' Set CompareMode before adding data
d.CompareMode = vbTextCompare

Minimal Example for Practical Use

Sub CountNormalizedKeys()

    Dim d As Object
    Dim v As Variant
    Dim key As String

    Set d = CreateObject("Scripting.Dictionary")
    d.CompareMode = vbTextCompare

    For Each v In Array("ABC", "abc", "ABC ", "XYZ")

        key = Trim$(CStr(v))

        ' Example: ignore blank data
        If Len(key) > 0 Then

            If d.Exists(key) Then
                d(key) = d(key) + 1
            Else
                d.Add key, 1
            End If

        End If

    Next v

    For Each v In d.Keys
        Debug.Print v, d(v)
    Next v

End Sub

In this example, the entries related to ABC are grouped together as 3 items.

Normalization Is Not Always Better When More Aggressive

For instance, if employee codes like abc and ABC are genuinely different codes, you must not ignore letter casing. Similarly, whether to treat full-width/half-width numbers, hyphens, or spaces as equivalent depends entirely on your business rules.

flowchart LR
A[Raw Data] --> B[Define Business Rules]
B --> C[NormalizeKey]
C --> D[Dictionary Key]
D --> E[Duplicate Count]

Practical Applications

• Removing leading and trailing spaces from email addresses
• Handling variations in department name notation
• Pre-import validation for CSV files
• Master data reconciliation
• Duplicate inspection for application data

In these scenarios, how you construct the keys impacts data quality much more than the syntax of the Dictionary itself.

When Working with Excel Ranges: For large datasets, reading cells one by one is less efficient than loading them into an array first for normalization and aggregation. Furthermore, instead of overwriting the original data during normalization, creating separate keys specifically for the Dictionary allows you to inspect data while preserving the original values.

Summary

• Define what constitutes a duplicate before performing checks.
• Trim$ can remove leading and trailing spaces.
• CompareMode allows you to specify the casing comparison policy.
• Over-normalizing risks treating genuinely distinct values as identical.
• Consolidating normalization into a single NormalizeKey function makes business rules easier to manage.

Official Information and Primary Sources

• Microsoft Learn: Dictionary object