Dictionaries and vba: when to use

[Nelson78]

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.

 

[Eric W]

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:

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:

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]

How about

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]

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]

How about

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

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

I suppose this part

+ Cl.Offset(, 2).Value

allows to sum in column C what is equal in combination column A/column B.

Am I correct?

 

[Fluff]

That's absolutely correct

 

[Nelson78]

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.

   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]

You could do that, or like this

= Dic(Cl.Value)(Cl.Offset(, 1).Value) + 1