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

Keys in dictionaries are always unique. Items can be more than one time with different keys. Look at this page to learn a lot about dictionaries: excelmacromastery.com/vba-dictionary Look espacialy in this part, to learn how to add new key/item pairs or update items for existing keys excelmacromastery.com/vba-dictionary/… Dictionaries also have the Exists() method, which is not present in collections. — Zwenn
– Zwenn, Commented Dec 14, 2020 at 13:19
Also keep in mind that the relationship is inverted. You don't remove duplicates from a dictionary because they are never actually added in the first place. The workflow should be (1) identify the unique key value, (2) check if that value has already been added (using Exists), (3) if not, then add to dict, otherwise do what ever action you need to do. — basodre
– basodre, Commented Dec 14, 2020 at 14:06

VBasic2008 · Accepted Answer · 2020-12-14 19:24:13Z

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

thank vbasic2008, now that i saw your sub i am starting to understand how this work, and thanks for the links

Collectives™ on Stack Overflow

vba dictionary remove duplicate

1 Answer 1

Dictionary Examples

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Dictionary Examples

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related