scripting.dictionary issue with 3rd colum values

[johnnyL]

Having an issue with summing the 3rd column.

This relates to Post 11.

I have the following code which takes multiple 3 column ranges, each 3 column range is separated with a blank column, and it takes the first column, keeps track of each unique value, sums the corresponding 2nd column, and for the 3rd column it keeps a counter for each time the 1st column value was found: Thanks to @Fluff !

Sub JonnyL1()
   Dim Cl As Range
   Dim Tmp As Variant
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("D3:V8").SpecialCells(xlConstants, xlTextValues)
         If Not .Exists(Cl.Value) Then
            .Item(Cl.Value) = Array(Cl.Offset(, 1).Value, 1)
         Else
            Tmp = .Item(Cl.Value)
            Tmp(0) = Tmp(0) + Cl.Offset(, 1).Value
            Tmp(1) = Tmp(1) + 1
            .Item(Cl.Value) = Tmp
         End If
      Next Cl
      Range("A2").Resize(.Count, 1).Value = Application.Transpose(.Keys)
      Range("B2").Resize(.Count, 2).Value = Application.Index(.Items, 0)
      Range("A1").Resize(.Count, 3).Sort Range("A1"), xlAscending, , , , , , xlYes
   End With
End Sub

Now instead of the 3rd column being a counter, I want it to be a sum of all of the 3rd column corresponding values to the first column. I tried the following slight alteration of the 1st code:

Sub JonnyL2()
   Dim Cl As Range
   Dim Tmp As Variant
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("D3:V8").SpecialCells(xlConstants, xlTextValues)
         If Not .Exists(Cl.Value) Then
            .Item(Cl.Value) = Array(Cl.Offset(, 1).Value, 1)
         Else
            Tmp = .Item(Cl.Value)
            Tmp(0) = Tmp(0) + Cl.Offset(, 1).Value
'            Tmp(1) = Tmp(1) + 1
            Tmp(1) = Tmp(1) + Cl.Offset(, 2).Value
            .Item(Cl.Value) = Tmp
         End If
      Next Cl
      Range("A2").Resize(.Count, 1).Value = Application.Transpose(.Keys)
      Range("B2").Resize(.Count, 2).Value = Application.Index(.Items, 0)
      Range("A1").Resize(.Count, 3).Sort Range("A1"), xlAscending, , , , , , xlYes
   End With
End Sub

But the 3rd column is not giving the correct totals.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Colour	Qty	Ordered
2	Black	14	14
3	Blue	3	2	Red	14	6		Green	8	3		Blue	1	3		Brown	4	10		Black	2	5
4	Brown	4	1	Blue	2	1		Purple	2	7		Red	4	2						Green	6	1
5	Green	19	5	Green	5	2		Orange	6	1										Orange	4	7
6	Orange	10	2					Red	0	5										Purple	8	3
7	Purple	10	8					Yellow	4	9
8	Red	18	12					Black	12	13
9	Yellow	4	1
10
11
Sheet1

It should be:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Colour	Qty	Ordered
2	Black	14	18
3	Blue	3	4	Red	14	6		Green	8	3		Blue	1	3		Brown	4	10		Black	2	5
4	Brown	4	10	Blue	2	1		Purple	2	7		Red	4	2						Green	6	1
5	Green	19	6	Green	5	2		Orange	6	1										Orange	4	7
6	Orange	10	8					Red	0	5										Purple	8	3
7	Purple	10	10					Yellow	4	9
8	Red	18	13					Black	12	13
9	Yellow	4	9
10
Sheet1

I thought the slight change I made would yield what I wanted, but it doesn't.

Any ideas?

 

[Fluff]

You need

.Item(Cl.Value) = Array(Cl.Offset(, 1).Value, Cl.Offset(, 2).Value)

 

[johnnyL]

Spot on as usual @Fluff !!!

Thank you once again!

 

[Fluff]

Glad to help & thanks for the feedback.