VBA: Using Dictionary or Arrays to sum based on multiple columns?

[Coyotex3]

Hi, I've been working on consolidating some data and was wondering what is the best approach using VBA Dictionaries or arrays to go from this range:

Book1
	A	B	C	D	E
1	Date	Name	Location	Bonus	Amount
2	10/12/2023	John Doe	New York	0	381
3	10/12/2023	Jane Doe	New York	0	126
4	10/12/2023	Jack Doe	New York	0	272
5	10/12/2023	John Doe	New York	0	522
6	10/12/2023	Jane Doe	New York	0	745
7	10/13/2023	John Doe	New York	0	144
8	10/13/2023	Jane Doe	New York	0	754
9	10/13/2023	Jack Doe	New York	0	263
10	10/13/2023	John Doe	New York	0	103
11	10/13/2023	Jane Doe	New York	0	525
12
13
Sheet1

To this range which contains a unique list of values based on Columns A:C(Date, Name, Location), and the fourth columns gives you the total amount for each combination(A sumifs in essence).

Book1
	J	K	L	M
1	Date	Name	Location	Amount
2	10/12/2023	John Doe	New York	903
3	10/12/2023	Jane Doe	New York	871
4	10/12/2023	Jack Doe	New York	272
5	10/13/2023	John Doe	New York	247
6	10/13/2023	Jane Doe	New York	1279
7	10/13/2023	Jack Doe	New York	263
8
9
Sheet1

 

[Eric W]

It can be done with a formula, or are you specifically looking for VBA?

Book1
	J	K	L	M
1	Date	Name	Location	Amount
2	10/12/2023	John Doe	New York	903
3	10/12/2023	Jane Doe	New York	871
4	10/12/2023	Jack Doe	New York	272
5	10/13/2023	John Doe	New York	247
6	10/13/2023	Jane Doe	New York	1279
7	10/13/2023	Jack Doe	New York	263
8
Sheet7

Cell Formulas
Range	Formula
J2:M7	J2	=LET(u,UNIQUE(A2:C11),a,INDEX(u,0,1),b,INDEX(u,0,2),c,INDEX(u,0,3),CHOOSE({1,2,3,4},a,b,c,SUMIFS(E2:E11,A2:A11,a,B2:B11,b,C2:C11,c)))
Dynamic array formulas.

 

[Coyotex3]

It can be done with a formula, or are you specifically looking for VBA?

Book1
	J	K	L	M
1	Date	Name	Location	Amount
2	10/12/2023	John Doe	New York	903
3	10/12/2023	Jane Doe	New York	871
4	10/12/2023	Jack Doe	New York	272
5	10/13/2023	John Doe	New York	247
6	10/13/2023	Jane Doe	New York	1279
7	10/13/2023	Jack Doe	New York	263
8
Sheet7

Cell Formulas
Range	Formula
J2:M7	J2	=LET(u,UNIQUE(A2:C11),a,INDEX(u,0,1),b,INDEX(u,0,2),c,INDEX(u,0,3),CHOOSE({1,2,3,4},a,b,c,SUMIFS(E2:E11,A2:A11,a,B2:B11,b,C2:C11,c)))
Dynamic array formulas.

Hi Eric, thank you for this. Looking specifically for VBA as I'm working on updating an existing code.

I was provided this code before which worked for what I needed. This one looked at a list of items in Column A, and ran calculations based of Column A. Have not been able to figure out a way of adding columns B and C to the If Not .Exists argument. Unsure if it can even be done.

Sub Example)
 Dim ar, a, i As Long
 ar = Cells(1).CurrentRegion
 
 With CreateObject("scripting.dictionary")
   For i = 2 To UBound(ar)
     If Not .Exists(ar(i, 1)) Then
       .Item(ar(i, 1)) = Array(ar(i, 1), ar(i, 2), ar(i, 3))
     Else
       a = .Item(ar(i, 1))
       a(2) = a(2) + ar(i, 3)
       .Item(ar(i, 1)) = a
     End If
   Next
   Cells(1, 8).Resize(.Count, 3) = Application.Index(.items, 0, 0)
 End With
End Sub

 

[lrobbo314]

How about this?

EXCEL
	A	B	C	D	E	F	G	H	I	J
1	Date	Name	Location	Bonus	Amount
2	10/12/2023	John Doe	New York	0	381		10/12/2023	John Doe	New York	903
3	10/12/2023	Jane Doe	New York	0	126		10/12/2023	Jane Doe	New York	871
4	10/12/2023	Jack Doe	New York	0	272		10/12/2023	Jack Doe	New York	272
5	10/12/2023	John Doe	New York	0	522		10/13/2023	John Doe	New York	247
6	10/12/2023	Jane Doe	New York	0	745		10/13/2023	Jane Doe	New York	1279
7	10/13/2023	John Doe	New York	0	144		10/13/2023	Jack Doe	New York	263
8	10/13/2023	Jane Doe	New York	0	754
9	10/13/2023	Jack Doe	New York	0	263
10	10/13/2023	John Doe	New York	0	103
11	10/13/2023	Jane Doe	New York	0	525
Sheet3

Sub test()
Dim r As Range:         Set r = Range("A2:E" & Range("A" & Rows.Count).End(xlUp).Row)
Dim AR() As Variant:    AR = r.Value2
Dim SD As Object:       Set SD = CreateObject("Scripting.Dictionary")

For i = 1 To UBound(AR)
    tmp = Join(Array(AR(i, 1), AR(i, 2), AR(i, 3)), ",")
    SD(tmp) = SD(tmp) + AR(i, 5)
Next i

Set r = Range("G2").Resize(SD.Count)
r.Value = Application.Transpose(SD.keys)
r.TextToColumns DataType:=xlDelimited, comma:=True

Set r = r.Offset(, UBound(AR, 2) - 2)
r.Value = Application.Transpose(SD.items)
End Sub

 

[Eric W]

lrobbo314 beat me to it, but here's my take, similar in concept:

Sub Coyotex3()
Dim ar As Variant, d As Object
Dim i As Long, k As Variant, out() As Variant, x As Variant

    ar = Cells(1).CurrentRegion
    
    Set d = CreateObject("Scripting.Dictionary")
    For i = 2 To UBound(ar)
        k = ar(i, 1) & "|" & ar(i, 2) & "|" & ar(i, 3)
        d(k) = d(k) + ar(i, 5)
    Next i
    
    ReDim out(1 To d.Count, 1 To 4)
    i = 0
    For Each x In d
        k = Split(x, "|")
        i = i + 1
        out(i, 1) = k(0)
        out(i, 2) = k(1)
        out(i, 3) = k(2)
        out(i, 4) = d(x)
    Next x
    
    Cells(2, "J").Resize(d.Count, 4) = out
            
End Sub

 

[Coyotex3]

@lrobbo314 Perfect! Works like a charm.

@Eric W Your solution works as well!

 

[Peter_SSs]

Some other options that may be considered if you have the GROUPBY function available. If you don't you should get sometime before too long.
I know that you don't want a formula approach but I have included one in Q1 (results spill into Q:T).
The GROUPBY function has lots of options so for a vba approach I have included a couple of those with results in G:I (basic grouping) and L:O (sorted ascending by date and descending by name and total included).

Sub GroupAndSum1()
  Dim d As Range
  
  Set d = Range("A1").CurrentRegion
  Range("G1").Formula2 = "=GROUPBY(" & d.Resize(, 3).Address & "," & d.Columns(5).Address & ",SUM,3,0)"
  With Range("G1").SpillingToRange
    .Value = .Value
    .Cells(1, 4).Value = d.Cells(1, 5).Value
  End With
End Sub

Sub GroupAndSum2()
  Dim d As Range
  
  Set d = Range("A1").CurrentRegion
  Range("L1").Formula2 = "=GROUPBY(" & d.Resize(, 3).Address & "," & d.Columns(5).Address & ",SUM,3,1,{1,-2})"
  With Range("L1").SpillingToRange
    .Value = .Value
    .Cells(1, 4).Value = d.Cells(1, 5).Value
  End With
End Sub

Coyotex3_1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Date	Name	Location	Bonus	Amount		Date	Name	Location	Amount		Date	Name	Location	Amount		Date	Name	Location
2	12/10/2023	John Doe	New York	0	381		12/10/2023	Jack Doe	New York	272		12/10/2023	John Doe	New York	903		12/10/2023	John Doe	New York	903
3	12/10/2023	Jane Doe	New York	0	126		12/10/2023	Jane Doe	New York	871		12/10/2023	Jane Doe	New York	871		12/10/2023	Jane Doe	New York	871
4	12/10/2023	Jack Doe	New York	0	272		12/10/2023	John Doe	New York	903		12/10/2023	Jack Doe	New York	272		12/10/2023	Jack Doe	New York	272
5	12/10/2023	John Doe	New York	0	522		13/10/2023	Jack Doe	New York	263		13/10/2023	John Doe	New York	247		13/10/2023	John Doe	New York	247
6	12/10/2023	Jane Doe	New York	0	745		13/10/2023	Jane Doe	New York	1279		13/10/2023	Jane Doe	New York	1279		13/10/2023	Jane Doe	New York	1279
7	13/10/2023	John Doe	New York	0	144		13/10/2023	John Doe	New York	247		13/10/2023	Jack Doe	New York	263		13/10/2023	Jack Doe	New York	263
8	13/10/2023	Jane Doe	New York	0	754							Total			3835		Total			3835
9	13/10/2023	Jack Doe	New York	0	263
10	13/10/2023	John Doe	New York	0	103
11	13/10/2023	Jane Doe	New York	0	525
12
Sheet1

Cell Formulas
Range	Formula
Q1:T8	Q1	=GROUPBY(A1:C11,E1:E11,SUM,3,1,{1,-2})
Dynamic array formulas.

 

[Coyotex3]

Some other options that may be considered if you have the GROUPBY function available. If you don't you should get sometime before too long.
I know that you don't want a formula approach but I have included one in Q1 (results spill into Q:T).
The GROUPBY function has lots of options so for a vba approach I have included a couple of those with results in G:I (basic grouping) and L:O (sorted ascending by date and descending by name and total included).

Sub GroupAndSum1()
  Dim d As Range
 
  Set d = Range("A1").CurrentRegion
  Range("G1").Formula2 = "=GROUPBY(" & d.Resize(, 3).Address & "," & d.Columns(5).Address & ",SUM,3,0)"
  With Range("G1").SpillingToRange
    .Value = .Value
    .Cells(1, 4).Value = d.Cells(1, 5).Value
  End With
End Sub

Sub GroupAndSum2()
  Dim d As Range
 
  Set d = Range("A1").CurrentRegion
  Range("L1").Formula2 = "=GROUPBY(" & d.Resize(, 3).Address & "," & d.Columns(5).Address & ",SUM,3,1,{1,-2})"
  With Range("L1").SpillingToRange
    .Value = .Value
    .Cells(1, 4).Value = d.Cells(1, 5).Value
  End With
End Sub

Coyotex3_1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Date	Name	Location	Bonus	Amount		Date	Name	Location	Amount		Date	Name	Location	Amount		Date	Name	Location
2	12/10/2023	John Doe	New York	0	381		12/10/2023	Jack Doe	New York	272		12/10/2023	John Doe	New York	903		12/10/2023	John Doe	New York	903
3	12/10/2023	Jane Doe	New York	0	126		12/10/2023	Jane Doe	New York	871		12/10/2023	Jane Doe	New York	871		12/10/2023	Jane Doe	New York	871
4	12/10/2023	Jack Doe	New York	0	272		12/10/2023	John Doe	New York	903		12/10/2023	Jack Doe	New York	272		12/10/2023	Jack Doe	New York	272
5	12/10/2023	John Doe	New York	0	522		13/10/2023	Jack Doe	New York	263		13/10/2023	John Doe	New York	247		13/10/2023	John Doe	New York	247
6	12/10/2023	Jane Doe	New York	0	745		13/10/2023	Jane Doe	New York	1279		13/10/2023	Jane Doe	New York	1279		13/10/2023	Jane Doe	New York	1279
7	13/10/2023	John Doe	New York	0	144		13/10/2023	John Doe	New York	247		13/10/2023	Jack Doe	New York	263		13/10/2023	Jack Doe	New York	263
8	13/10/2023	Jane Doe	New York	0	754							Total			3835		Total			3835
9	13/10/2023	Jack Doe	New York	0	263
10	13/10/2023	John Doe	New York	0	103
11	13/10/2023	Jane Doe	New York	0	525
12
Sheet1

Cell Formulas
Range	Formula
Q1:T8	Q1	=GROUPBY(A1:C11,E1:E11,SUM,3,1,{1,-2})
Dynamic array formulas.

Peter thanks for this option! Sadly I do not have GroupBy available yet. Still waiting for those to be available to regular users!