Dictionaries and vba: when to use

Nelson78

Active Member
Joined
Sep 11, 2017
Messages
470
Hello everybody.

I've been studying some of the solutions found in mrexcel.com about dictionaries.
Having applied them "blindly" to my issues with success, I've decided to understand better their dynamic.

Suppose I've a table like this:
https://imgur.com/h2sfGql

How can I build a dictionaries which helps me to calculate how many products - for any of the two CD27/AZ15 model - have been sold for any of the three agents?

To me, understanding this, could be a solid step to start.

Thank's.
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,211
As any first year CS student can tell you, designing an appropriate data structure is key to developing an efficient and robust system. Your layout is very similar to basic database tables. If you did create your table in a database, there are powerful SQL instructions that would enable you to create quite sophisticated queries. Your question would roughly look like:

Code:
SELECT SUM(Quantity)
  WHERE Agent = "Smith" and
         Model = "AZ15"
Although Excel can do some SQL, it's really not designed for it, like say, Access.

If you do stick with Excel, you could use a dictionary of dictionaries. For example:

Code:
Sub test1()
Dim AgentDic As Object, r As Long, Agent As String, Model As String, quantity As Long


    Set AgentDic = CreateObject("Scripting.Dictionary")
    
    For r = 2 To 23
        Agent = Cells(r, "A")
        Model = Cells(r, "B")
        quantity = Cells(r, "C")
        
        If Not AgentDic.exists(Agent) Then
            AgentDic.Add Agent, CreateObject("Scripting.Dictionary")
        End If
        
        AgentDic(Agent)(Model) = AgentDic(Agent)(Model) + quantity
    Next r
    
    Debug.Print AgentDic("Maxwell")("AZ15")
    
End Sub
This is an example only, you need to carefully look at all your requirement. But first make sure you're using the right tool.

Good luck!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,058
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
Sub Nelson78()
   Dim Dic As Object
   Dim Cl As Range
   Dim Ky As Variant
   
   Set Dic = CreateObject("scripting.dictionary")
   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      If Not Dic.Exists(Cl.Value) Then Dic.Add Cl.Value, CreateObject("scripting.dictionary")
      Dic(Cl.Value)(Cl.Offset(, 1).Value) = Dic(Cl.Value)(Cl.Offset(, 1).Value) + Cl.Offset(, 2).Value
   Next Cl
   
   For Each Ky In Dic.keys
      With Range("F" & Rows.Count).End(xlUp)
         .Offset(1).Resize(Dic(Ky).Count).Value = Ky
         .Offset(1, 1).Resize(Dic(Ky).Count, 2).Value = Application.Transpose(Array(Dic(Ky).keys, Dic(Ky).items))
      End With
   Next Ky
End Sub
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,092
There are several ways to do it. If using dictionaries or collections, you could combine columns A:B. And then do SumIf()'s for each unique concatenated value.

Manually, one could add column C as a concatenation of columns A:B. Then in another column, get the total sum. e.g. =SUMIF($C$2:$C$24,C2,$D$2:$D$24)

In the dictionary/collection method, the column C values would be unique. Iterate those and use sumif by replacing C2 with the unique A:B value.

Of course ADO can be used for query too.
 

Nelson78

Active Member
Joined
Sep 11, 2017
Messages
470

ADVERTISEMENT

How about
Code:
Sub Nelson78()
   Dim Dic As Object
   Dim Cl As Range
   Dim Ky As Variant
   
   Set Dic = CreateObject("scripting.dictionary")
   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      If Not Dic.Exists(Cl.Value) Then Dic.Add Cl.Value, CreateObject("scripting.dictionary")
      Dic(Cl.Value)(Cl.Offset(, 1).Value) = Dic(Cl.Value)(Cl.Offset(, 1).Value) + Cl.Offset(, 2).Value
   Next Cl
   
   For Each Ky In Dic.keys
      With Range("F" & Rows.Count).End(xlUp)
         .Offset(1).Resize(Dic(Ky).Count).Value = Ky
         .Offset(1, 1).Resize(Dic(Ky).Count, 2).Value = Application.Transpose(Array(Dic(Ky).keys, Dic(Ky).items))
      End With
   Next Ky
End Sub

Thank's all for your contribuitions.

About this line

Code:
Dic(Cl.Value)(Cl.Offset(, 1).Value) = Dic(Cl.Value)(Cl.Offset(, 1).Value) + Cl.Offset(, 2).Value

I suppose this part
Code:
+ Cl.Offset(, 2).Value
allows to sum in column C what is equal in combination column A/column B.

Am I correct?
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,058
Office Version
  1. 365
Platform
  1. Windows
That's absolutely correct :)
 

Nelson78

Active Member
Joined
Sep 11, 2017
Messages
470
That's absolutely correct :)

Ok.

And applying the same concept, with this I can count how many times any single combination column A/column B occurs.

Code:
   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      If Not Dic.exists(Cl.Value) Then Dic.Add Cl.Value, CreateObject("scripting.dictionary")
      Dic(Cl.Value)(Cl.Offset(, 1).Value) = Dic(Cl.Value)(Cl.Offset(, 1).Value) + Cl.Offset(, 2).Count
   Next Cl
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,058
Office Version
  1. 365
Platform
  1. Windows
You could do that, or like this
Code:
= Dic(Cl.Value)(Cl.Offset(, 1).Value) + 1
 

Watch MrExcel Video

Forum statistics

Threads
1,108,585
Messages
5,523,734
Members
409,533
Latest member
rinogjb

This Week's Hot Topics

Top