Countifs between different Sheets via Dictionary

[MK91]

Dear Excel-Experts,

after failing on my own with various builds, I decided to ask the experts for advice


What do i want to do?

Database
	A	B
1	WS DB
2	KEY	toCount
3	A	222
4	B	456
5	E	358
6	C	125
7	H	256
8	D	897
9	S	799
10	C	562
11	A	489
12	B	456
13	A	222
14	E	358
15	G	114
16	F	875
17	C	763
Tabelle1

SHOW
	D	E
2	KEY	Amount of toCount (different)
3	A	2
4	B	1
5	C	3
6	D	1
7	E	1
Tabelle1

My goal is to fill the column "Amout of toCount (different)" on sheet: "SHOW"

This column should tell me the number of all different "toCount" values of the sheet: "Database".

Example:
Key A
In the database A is mentioned 3 times, but the first and the third time "toCount"-Value is the same (222) so the result is 2.
Key B
mentioned 2 times - both have the same "toCount" -> result = 1
Key C
mentioned 4 times with 3 different "toCount" -> result = 3


I have successfully written a program in which I query a specific key, but I can't manage to convert it to a list (on another sheet):

Sub simpleCountifDictionary()
    Dim dictX As Object, strX As String, vARR_DB As Variant
    
    Set dictX = CreateObject("scripting.Dictionary")
    
    strX = "EXAMPLE"
    
    Call DB__INFO
    
    With WS_DB
        
        vARR_DB = .Range(.Cells(Z_DB__Start, S_DB__Start), .Cells(Z_DB__End, S_DB__End))
    
        For z = 1 To UBound(vARR_DB)
            If vARR_DB(z, S_DB__Zuo__Person) = strX Then
                dictX(vARR_DB(z, S_DB__PNr)) = vARR_DB(z, S_DB__PNr) + 1
            End If
        Next z

    End With
        
    Debug.Print dictX.Count
End Sub

WS_DB, Z_DB__Start ... are declared in "DB_INFO"
The code example does not exactly match the tables above - they are anoymized. It should only present my progress.


Maybe someone can help me.


Many thanks in Advance
MK91

 

[DanteAmor]

Hi @MK91, Thanks for posting on the forum.

Please try the following solution with 2 dictionaries:

Sub simpleCountifDictionary()
  Dim dic1 As Object, dic2 As Object
  Dim a As Variant
  Dim combo As String
  Dim i As Long
  Dim c As Range
 
  Set dic1 = CreateObject("Scripting.Dictionary")
  Set dic2 = CreateObject("Scripting.Dictionary")
  With Sheets("SHOW")
    a = .Range("A2", .Range("B" & Rows.Count).End(3)).Value
 
    For i = 1 To UBound(a)
      combo = a(i, 1) & "|" & a(i, 2)
      If Not dic1.exists(a(i, 1)) Then
        dic1(a(i, 1)) = 1
        dic2(combo) = Empty
      Else
        If Not dic2.exists(combo) Then
          dic2(combo) = Empty
          dic1(a(i, 1)) = dic1(a(i, 1)) + 1
        End If
      End If
    Next
 
    'Amount of toCount (different) Only from the data in column D
    For Each c In .Range("D2", .Range("D" & Rows.Count).End(3))
      c.Offset(, 1).Value = dic1(c.Value)
    Next
 
    'All KEYS
    .Range("G2").Resize(dic1.Count, 2).Value = Application.Transpose(Array(dic1.keys, dic1.items))
  End With
End Sub

The result in the SHOW sheet like this:

Dante Amor
	A	B	C	D	E	F	G	H
1	KEY	toCount		KEY	Amount of toCount (different)		ALL KEY	Amount of toCount (different)
2	A	222		A	2		A	2
3	A	489		B	1		B	1
4	B	456		C	3		E	1
5	A	222		D	1		C	3
6	B	456		E	1		H	1
7	E	358					D	1
8	C	125					S	1
9	H	256					G	1
10	D	897					F	1
11	S	799
12	C	562
13	E	358
14	G	114
15	F	875
16	C	763
SHOW

I hope to hear from you soon.
Respectfully
Dante Amor
.

 

[MK91]

Hey Dante,

thank you very much for your fast answer. Your code is working very well .

Perhaps you can explain one or two points in more detail so that I can understand them and learn from them.

First:
I don't really understand how you built in range B. The only time you use it is at this position (a(i,2)):

combo = a(i, 1) & "|" & a(i, 2)

But where/how do you transfer the amount of hits into the dictionary1?



Second:
Can you please explain the role of dic2 explain? I just see how you relate it to "combo" on "Empty" - but I don't understand why.

dic2(combo) = Empty

If Not dic2.exists(combo) Then
          dic2(combo) = Empty

Last:
Let's assume the first table would be extended by a column "C" and I also want to have those counted for example in column "F" & "I". Is it possible to modify the code in a simple way?


Finally, thanks again for your help! Your code applied to my application works great, but I would like to understand more of it to master it myself and apply it to other projects.


Kind regards
MK91

 

[DanteAmor]

Hi @MK91:

I'll explain how it works:

- I have created 2 dictionaries, in dic1 I store the unique values of column A. For example, in the data you have 3 times the letter "A", in dic1 only one letter "A" is stored as an index (this is very important, in the dictionary you store the indices (keys) and you can associate a piece of information (item) with it. In this case, I store the number 1, that is, I start the counter at 1.

      If Not dic1.exists(a(i, 1)) Then
        'If it doesn't exist, then I add the data to the dictionary and put the number 1 because the counter starts at 1.
        dic1(a(i, 1)) = 1

- 'In something I called "combo" I have the combination of the data from column A and column B

combo = a(i, 1) & "|" & a(i, 2)

I use the separator "|" between both data, for the following: 1) knowing where data1 is and where data2 is, 2) it is an elegant way to distinguish each data and 3) very important, without the separator it could be that the combination of both can be repeated , for example: data1: AB, data2: 3, combined: AB3
This could be confused with something like data1: A, data2: B3, combined: AB3, you would have 2 times the combination and it is not true.
So the correct is:
AB|3
A|B3

- So in dic2, I store the combo, returning to the example of the letters "A", if the letter "A" does not exist in dic1, it will not exist in dic2 either, that is why I add the combo in dic2 and associate it an emtpy value, since for this scenario a data in the dic2 is not necessary

      If Not dic1.exists(a(i, 1)) Then
        'If it doesn't exist, then I add the data to the dictionary and put the number 1 because the counter starts at 1.
        dic1(a(i, 1)) = 1
        dic2(combo) = Empty

Example:

varios 09mar2023.xlsm
	A	B
1	KEY	toCount
2	A	222
SHOW

In the example above, when the reading starts, the letter "A" does not exist in dec1, so it is added to dic1. The combo "A|222", since the letter "A" does not exist, then it is certain that the combo does not exist in dic2, that is why it is added in dic2.
We continue with record 2

varios 09mar2023.xlsm
	A	B
1	KEY	toCount
2	A	222
3	A	489
SHOW

      If Not dic1.exists(a(i, 1)) Then
        dic1(a(i, 1)) = 1
        dic2(combo) = Empty
      Else
        'The letter "A" already exists in Dic1
        If Not dic2.exists(combo) Then
          '"A | 489" not exists, then it is added to dic2
          dic2(combo) = Empty     'with empty since an item is not required
          'As it is a new combo, so we increased the counter of the letter "A", it was at 1, plus 1, so now the counter of "A" is equal to 2.
          dic1(a(i, 1)) = dic1(a(i, 1)) + 1
        End If
      End If
    Next

I hope it helps you.