Expanding table via Scripting dictionary

[Michael M]

Hi All
I've been trying to improve my knowledge of Dictionaries and have been using this code kindly submitted by @Rick Rothstein some time ago.
How can I expand the data so that it also sums column "C" as well. Any help would be much appreciated

Sub Consolidate()
  Dim R As Long, Data As Variant
  Data = Range("A2", Cells(Rows.Count, "B").End(xlUp))
  With CreateObject("Scripting.Dictionary")
    For R = 1 To UBound(Data)
      .Item(Data(R, 1)) = .Item(Data(R, 1)) + Data(R, 2)
     Next R
    Range("D2").Resize(.Count) = Application.Transpose(.Keys)
    Range("E2").Resize(.Count) = Application.Transpose(.Items)
  End With
End Sub

 

[DanteAmor]

Try the next one, no condition with a single cycle, I think it's a bit faster.

Sub Consolidate4_d()
  Dim R As Long, Data As Variant, dic As Object, dic2 As Object
  Data = Range("A2", Cells(Rows.Count, "C").End(xlUp))
  Set dic = CreateObject("Scripting.Dictionary")
  Set dic2 = CreateObject("Scripting.Dictionary")
  For R = 1 To UBound(Data)
    dic(Data(R, 1)) = dic(Data(R, 1)) + Data(R, 2)
    dic2(Data(R, 1)) = dic2(Data(R, 1)) + Data(R, 3)
  Next
  Range("D2").Resize(dic.Count, 2).Value = Application.Transpose(Array(dic.keys, dic.items))
  Range("F2").Resize(dic.Count, 1).Value = Application.Transpose(dic2.items)
End Sub

 

[Michael M]

@DanteAmor
Yep, you are correct, your latest is about 37% faster than that of Joes....

Scripting Dictionary Example multi.xls
	H	I	J	K
2	DanteAmor	=	0.34765625	seconds
3	Peter_SSs	=	0.421875	seconds
4	Fluff	=	0.3125	seconds
5	JoeMo	=	0.2890265	seconds
6	DanteAmor V2	=	0.18359375	seconds
Sum Data

 

[johnnyL]

Hi All
I've been trying to improve my knowledge of Dictionaries and have been using this code kindly submitted by @Rick Rothstein some time ago.
How can I expand the data so that it also sums column "C" as well. Any help would be much appreciated

@Michael M I am confused. You asked for 'sum of column C'. All of the great code examples here paste the entire Column C. None of them 'sum' the C Column as you requested.

 

[Peter_SSs]

I am confused. You asked for 'sum of column C'. All of the great code examples here paste the entire Column C. None of them 'sum' the C Column as you requested.

It is implicit from the original code provided, which sums column B per unique item in column A, that the request was to also sum column C per unique item in column A

 

[Michael M]

@johnnyL
No, they ALL work as requested....some slightly faster than others !!
It may depend on what your data setup is...If all the names in Col "A" are unique then col "C" will be the same as the original table

 

[johnnyL]

I guess I use different terminology. I don't see any 'summing' of any columns here in this thread. My apologies!

 

[Michael M]

Ok, try it with this small data set... you will see the result of summed cells in adjacent columns

Scripting Dictionary Example multi.xls
	A	B	C
1	CustomerID	Amount	Items
2	A1	$ 8,701	158
3	B1	$ 9,142	71
4	C1	$ 5,115	35
5	D1	$ 2,400	23
6	A1	$ 6,500	42
7	B1	$ 5,000	29
8	B1	$ 4,567	50
Sum Data

 

[DanteAmor]

I don't see any 'summing' of any columns here in this thread.

Check all the codes, they have the following instruction to load into a Data matrix 3 columns.

Data = Range("A2", Cells(Rows.Count, "C").End(xlUp))

Column 3 represents column C.
All codes have a loop that sums the values in column C into a dictionary that has the unique values.

.Item(Data(R, 1)) = .Item(Data(R, 1)) + Data(R, 3)

Tmp(1) = Tmp(1) + Data(R, 3)

.Item(Data(R, 1)) = Array(.Item(Data(R, 1))(0) + Data(R, 2), .Item(Data(R, 1))(1) + Data(R, 3))

dic2(Data(R, 1)) = dic2(Data(R, 1)) + Data(R, 3)

The codes have different structure and @Michael M was checking which one is the fastest.

 

[johnnyL]

Ok, try it with this small data set... you will see the result of summed cells in adjacent columns

Scripting Dictionary Example multi.xls
	A	B	C
1	CustomerID	Amount	Items
2	A1	$ 8,701	158
3	B1	$ 9,142	71
4	C1	$ 5,115	35
5	D1	$ 2,400	23
6	A1	$ 6,500	42
7	B1	$ 5,000	29
8	B1	$ 4,567	50
Sum Data

Ahaaaa! I didn't have that sample you provided there, so I just made up totally random, Non repeating values for Columns A - C and tested the codes being offered here, shocker, nothing was being summed because all values were different. :rollseyes

Now that I know how the game is being played, lemme see what I can come up with.

Thank you all for such a fun site!

 

[Michael M]

Yeah...the ..Non repeating values.....will make the dictionary process fairly redundant.
My sample ashould show how the process works...