Scripting Dictionary help

Hello Excel Forum,

I'm trying to build a very basic de-duplication tool by using two dictionary objects. Each company on my list (there are two lists - a source and one to check against) has a unique ID, which I intend to use as the key in each dictionary (I haven't included the code for generating both dictionary objects).

The problem I'm having is that my intended key is being stored as a value (when I 'watch' the variable the value shows as the intended key). However, when I switch the order of my code I get 'key already used' errors. In the code below column "B" contains the unique ID and column "E" contains the company name. (SICompanies and SIZips are dimensioned as objects):

    Set SICompanies = CreateObject("Scripting.Dictionary")
    Set SIZips = CreateObject("Scripting.Dictionary")

    For i = 1 To recCountALL
        With ws
            'add SI companies and zips to collections using IDs on sheet as collection keys
            If .Range("B1").Offset(i, 0).Value > 0 Then
                SICompanies.Add .Range("B1").Offset(i, 0).Value, .Range("E1").Offset(i, 0).Value
                SIZips.Add .Range("B1").Offset(i, 0).Value, .Range("E1").Offset(i, 6).Value
            End If
        End With
    Next i

Any help/advice would be much appreciated.

Thanks
Williams485

Wil,
I am not sure I understand the issue, but on these lines

SICompanies.Add .Range("B1").Offset(i, 0).Value, .Range("E1").Offset(i, 0).Value
                SIZips.Add .Range("B1").Offset(i, 0).Value, .Range("E1").Offset(i, 6).Value

You seem to have used the same key twice (.Range("B1").Offset(i, 0).Value) Unless ofcourse you have two different keys.If this is the full code, why you are adding the items with no reason? My guess is you need to set-up the dictioanary using the "Exists" method, so if the key exists, or not exists, do something i.e, In your case, you are adding the items(values)

I've now got to the bottom of my issue. The solution was twofold. Firstly, when I first posted I thought that my code was storing my intended item as the key - I thought this because the 'watch' window showed the key in the value column. However, I've since learned that despite the misleading display, my items, i.e. the company names, were being stored correctly.

The second part of my problem was my incorrect use of the dictionary object functions and properties: I hadn't paid enough attention to how I was accessing items within the dictionary. I now use an array to store the items of the dictionaries, which pass these to other functions.

So, the final code to build the dictionaries is as follows:

    Dim SICompanies As Scripting.Dictionary, SIZips As Scripting.Dictionary, _
        WynCompanies As Scripting.Dictionary, WynZips As Scripting.Dictionary
    Dim SIItems() As Variant, WynItems() As Variant, WynZipItems() As Variant, SIZipItems() As Variant
    Dim contents As Variant
    Dim cell As Range

    contents = ws.Range("A2:D" & recCountALL).Value

    Set SICompanies = New Scripting.Dictionary
    Set SIZips = New Scripting.Dictionary
    Set WynCompanies = New Scripting.Dictionary
    Set WynZips = New Scripting.Dictionary
    
    'loop through array
    For i = 1 To UBound(contents, 1)
        
        'if 1st column (Wyn ID) of array is not empty add to dictionary using wyn ID as key
        If contents(i, 1) > 0 Then
            WynCompanies.Add contents(i, 1), contents(i, 3)
            WynZips.Add contents(i, 1), contents(i, 4)
        Else 'if wyn ID is empty, must be SI record
            SICompanies.Add contents(i, 2), contents(i, 3)
            SIZips.Add contents(i, 2), contents(i, 4)
        End If
        
    Next i
    
    WynItems = WynCompanies.Items
    SIItems = SICompanies.Items
    WynZipItems = WynZips.Items
    SIZipItems = SIZips.Items
    
    For i = 0 To UBound(SIItems)
        maxDupeScore = 0
        For j = 0 To UBound(WynItems)
            
             ' more code here

            For Each cell In ws.Range("A1").CurrentRegion.Columns(3).Cells
                If cell.Value = SIItems(i) Then
                    cell.Offset(0, 2).Value = maxDupeScore
                    Exit For
                End If
            Next cell
        Next j
    Next i

Hope this helps someone else...

Williams485

Glad you have got round in the end.Dictioanary is not simple concept to understand as there not many materials written on them and only a handful of people actually use them on thier code. What I know so far comes from this site's contributors.

Thanks, JP. Looks very useful and informative.