Dictionary object - Storing Multiple Values in One Key

[Mike756]

Hi!

I use the code below to store ID's and the corresponding email addresses. Now I need to add a name too and I realized that the Dictionary object can only have one value. I tried adapting my code using a Class Module, but my VBA knowledge is weak. Could someone please help me out a little?

Account #	Email	Name
123​	foo@bar.co	Foo Bar

  Dim wb As Workbook, ws As Worksheet
    Dim iLastRow As Long, i As Long
    Dim dictID As Object, ID, addr As String

    Set dictID = CreateObject("Scripting.Dictionary")
 
    ' get list of IDS
    Set wb = ThisWorkbook
    Set ws = wb.Sheets(WS_ID)
    iLastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
    For i = 2 To iLastRow
        ID = Trim(ws.Cells(i, "A"))
        addr = Trim(ws.Cells(i, "B"))
        If dictID.exists(ID) Then
            MsgBox ID & " is duplicated", vbCritical, "Duplicate ID"
            Exit Sub
        ElseIf InStr(1, addr, "@") > 0 Then
            dictID.Add ID, addr
        End If
    Next

Thank you very much!

 

[DanteAmor]

Hi @Mike756. Thanks for posting on MrExcel board.

You can add several concatenated items to the same dictionary key.
In the following code I show you how to add the email and the name concatenated and separated by "|".
In the next part of the code I show you how to read the data from the dictionary.

Sub id_email()
  Dim wb As Workbook, ws As Worksheet
  Dim iLastRow As Long, i As Long
  Dim dictID As Object
  Dim sID As String, sData As String, sEmail As String, sName As String
  Dim ky As Variant
  
  Set dictID = CreateObject("Scripting.Dictionary")
  
  ' get list of IDS
  Set wb = ThisWorkbook
  Set ws = wb.Sheets("WS_ID")
  iLastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
  For i = 2 To iLastRow
    sID = Trim(ws.Cells(i, "A"))
    sData = Trim(ws.Cells(i, "B").Value) & "|" & Trim(ws.Cells(i, "C").Value)
    If dictID.exists(sID) Then
      MsgBox sID & " is duplicated", vbCritical, "Duplicate ID"
      Exit Sub
    ElseIf InStr(1, sData, "@") > 0 Then
      dictID.Add sID, sData
    End If
  Next
  '
  '...
  'To read the data from the dictionary:
  For Each ky In dictID.keys
    sID = ky
    sEmail = Split(dictID(ky), "|")(0)
    sName = Split(dictID(ky), "|")(1)
    MsgBox "Id: " & sID & vbCr & _
           "Email: " & sEmail & vbCr & _
           "Name: " & sName
  Next
End Sub

If you want to use the class check the following example is very similar:

Retrieve multiple values using VBA from dictionary

Hi, Long time lurker, first time poster and VBA newb so bear with me. I am looking for a way to automate some reports that are currently done manually. Sheet1 contains a list of WO numbers in Column C. Sheet2 contains a list of potential matching WO numbers in Column A. Looking to take the WO...

www.mrexcel.com

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

 

[Tupe77]

Although the dictionary key can contain only one item, the item can also be an array, list or even another dictionary.

 

[JEC]

Yes like

dictID.Add sID, array("email","name")

 

[Mike756]

Hi @Mike756. Thanks for posting on MrExcel board.

You can add several concatenated items to the same dictionary key.
In the following code I show you how to add the email and the name concatenated and separated by "|".
In the next part of the code I show you how to read the data from the dictionary.

Sub id_email()
  Dim wb As Workbook, ws As Worksheet
  Dim iLastRow As Long, i As Long
  Dim dictID As Object
  Dim sID As String, sData As String, sEmail As String, sName As String
  Dim ky As Variant
 
  Set dictID = CreateObject("Scripting.Dictionary")
 
  ' get list of IDS
  Set wb = ThisWorkbook
  Set ws = wb.Sheets("WS_ID")
  iLastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
  For i = 2 To iLastRow
    sID = Trim(ws.Cells(i, "A"))
    sData = Trim(ws.Cells(i, "B").Value) & "|" & Trim(ws.Cells(i, "C").Value)
    If dictID.exists(sID) Then
      MsgBox sID & " is duplicated", vbCritical, "Duplicate ID"
      Exit Sub
    ElseIf InStr(1, sData, "@") > 0 Then
      dictID.Add sID, sData
    End If
  Next
  '
  '...
  'To read the data from the dictionary:
  For Each ky In dictID.keys
    sID = ky
    sEmail = Split(dictID(ky), "|")(0)
    sName = Split(dictID(ky), "|")(1)
    MsgBox "Id: " & sID & vbCr & _
           "Email: " & sEmail & vbCr & _
           "Name: " & sName
  Next
End Sub

If you want to use the class check the following example is very similar:

Retrieve multiple values using VBA from dictionary

Hi, Long time lurker, first time poster and VBA newb so bear with me. I am looking for a way to automate some reports that are currently done manually. Sheet1 contains a list of WO numbers in Column C. Sheet2 contains a list of potential matching WO numbers in Column A. Looking to take the WO...

www.mrexcel.com

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

Thank you Sir!

 

[Mike756]

Yes like

dictID.Add sID, array("email","name")

Thanks! I used an array now.