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 shoulddetermine what constitutes a duplicate and normalize the keysbefore 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.

To remove leading and trailing spaces,

consolidate key generation into a single location.

Private Function NormalizeKey(ByVal value As Variant) As String

    ' 前後の半角スペースを取り除いて文字列化する
    NormalizeKey = Trim$(CStr(value))

End Function

On the aggregation side,

Dim key As String
key = NormalizeKey(v)

process the data before adding it to the Dictionary.

To treat uppercase and lowercase letters as identical,

CompareMode convert vbTextCompare to

Set d = CreateObject("Scripting.Dictionary")

' データを追加する前に設定する
d.CompareMode = vbTextCompare

. This allows you to compare ABC and abc as the same key.

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))

        ' 空白だけのデータを数えない例
        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

• 00123full-width and half-width numbers,123

• hyphens present or absent,

• spaces present or absent,

• as equivalent depends entirely on your business rules.

Practical Applications

flowchart LR
A[元データ] --> B[業務ルールを決める]
B --> C[NormalizeKey]
C --> D[DictionaryのKey]
D --> E[重複件数]

Customer code deduplication

• 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 RangesFor 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

Additional context or placeholder.

• Final placeholder segment.