Dictionaries and vba: when to use

Nelson78

Well-known Member
Joined
Sep 11, 2017
Messages
525
Office Version
  1. 2007
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.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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!
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
That's absolutely correct :)
 
Upvote 0
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
 
Upvote 0
You could do that, or like this
Code:
= Dic(Cl.Value)(Cl.Offset(, 1).Value) + 1
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top