vba dictionary remove duplicate

Question

I'm trying to learn how dictionaries works, I know they are similar to collections but I can't quite understand some things, now this is my test work:

i have some values like this

then i put all in a dictionary with a module beacuse i want to compare a value in the column to the other one

Sub dict()
Dim diz As New Dictionary
Dim ora As classe
Dim rg As Range

Set rg = Foglio4.Range("b2:b700")

For i = 2 To 700
Set ora = New classe
ora.old = rg.Cells(i, 1).Value
ora.turn = rg.Cells(i, 2).Value
diz.Add ora, i
Next i

If diz.Exists("4072") = True Then
MsgBox "esiste"
End If


End Sub

in diz i have all values like, diz item (1) "4072","1602"

but there are duplicates that i want to remove

just i can't figure out how

i tried to use the function Exists but it give me back always false

the goal is this

i search a number in a dictionary and i have back the value in the other column

i hope u can help me to better undestand dictionary

thank you in advance

Answer 1

Dictionary Examples

• Learn about the Dictionary object here, here and here

The Code

Option Explicit

Sub dict()
    
    Dim diz As New Dictionary
    Dim ora As classe
    Dim rg As Range
    Dim i As Long  

    Set rg = Foglio4.Range("B2:C700")
    
    For i = 2 To 700
        Set ora = New classe
        ora.old = rg.Cells(i, 1).Value
        ora.turn = rg.Cells(i, 2).Value
        If Not diz.Exists(ora.old) Then
            diz.Add ora.old, ora.New
        End If
    Next i
    
    If diz.Exists("4072") = True Then
        MsgBox "esiste"
    End If

End Sub

' Strings
Sub dict2()
    
    Dim diz As New Dictionary
    Dim Data As Variant
    Dim rg As Range
    Dim i As Long
    
    Set rg = Foglio4.Range("B2:C700")
    Data = rg.Value
    
    For i = 1 To UBound(Data)
        If Not diz.Exists(CStr(Data(i, 1))) Then
            diz.Add CStr(Data(i, 1)), CStr(Data(i, 2))
        End If
    Next i
    
    If diz.Exists("4072") Then
        MsgBox "The Key '4072' contains the value '" & diz("4072") & "'."
    End If

End Sub

'Numbers
Sub dict3()
    
    Dim diz As New Dictionary
    Dim Data As Variant
    Dim rg As Range
    Dim i As Long
    
    Set rg = Foglio4.Range("B2:C700")
    Data = rg.Value
    
    For i = 1 To UBound(Data)
        If Not diz.Exists(Data(i, 1)) Then
            diz.Add Data(i, 1), Data(i, 2)
        End If
    Next i
    
    If diz.Exists(4072) Then
        MsgBox "The Key '4072' contains the value '" & diz(4072) & "'."
    Else
        MsgBox "The Key '4072' doesn't exist."
    End If

End Sub

' No reference needed. No dictionary variable, no range variable.
Sub dict4()
    With CreateObject("Scripting.Dictionary")
        Dim Data As Variant: Data = Foglio4.Range("B2:C700").Value
        Dim i As Long
        For i = 1 To UBound(Data)
            If Not .Exists(Data(i, 1)) Then
                .Add Data(i, 1), Data(i, 2)
            End If
        Next i
        If .Exists(4072) Then
            MsgBox "The Key '4072' contains the value '" & .Item(4072) & "'."
        Else
            MsgBox "The Key '4072' doesn't exist."
        End If
        'Debug.Print "Keys" & vbLf & Join(.Keys, vbLf)
        'Debug.Print "Values (Items)" & vbLf & Join(.Items, vbLf)
    End With
End Sub