How to mimic a pivot table without a pivot table?

[nutra]

I have a list of items and their associated quantities, many items appearing multiple times. I need a concise list that summarizes each item and sums all of its quantities.

The obvious solution is a pivot table. However, I update this list frequently and for some reason the pivot table is difficult to update. is there a function or simple vba code that I could put into this workbook that would work better than my unflexible pivot table?

Thanks.

 

[northwolves]

Still suggest using "Dictionary,test it please:

Sub mimicpiv2()
Application.ScreenUpdating = 0
Dim arr, i As Long, n As Long, t As Single, z As New Dictionary
t = Timer
n = [a65536].End(3).Row
arr = [a1].Resize(n, 2)
For i = 1 To n
z(arr(i, 1)) = z(arr(i, 1)) + arr(i, 2)
Next
n = z.Count
[h1].Resize(n, 2) = Application.Transpose(Array(z.keys, z.items))
[h2].Resize(n - 1, 2).Sort [h2]
Application.ScreenUpdating = 1
MsgBox Format(Timer - t, "0.000")
End Sub

 

[northwolves]

It's a bit faster because sorting of Raw data was not needed.

 

[Legacy 14611]

Still suggest using "Dictionary,test it please:

Dim arr, i As Long, n As Long, t As Single, z As New Dictionary

Unable to test it because my software won't recognize the part marked red.

It's a bit faster because sorting of Raw data was not needed.

Sorting raw data does not ipso facto (by reason of this fact alone) mean slower code. Depends on a number of other things, e.g. some alternatives might cause slower code than does the sorting procedure.

In the operational codes I've been able to test so far in this thread, the scripting dictionary and the sorting approach seem to give somewhat comparable run times. Depends on the data.

However, the Excel sort runs significantly faster on sorted, partially sorted, or even roughly ordered data than it does on completely randomized data, whereas it makes little difference to the scripting dictionary. And since it seems quite a lot of Excel data is at least roughly ordered in some way then ...

 

[northwolves]

Unable to test it because my software won't recognize the part marked red.Sorting raw data does not ipso facto (by reason of this fact alone) mean slower code. Depends on a number of other things, e.g. some alternatives might cause slower code than does the sorting procedure.

In the operational codes I've been able to test so far in this thread, the scripting dictionary and the sorting approach seem to give somewhat comparable run times. Depends on the data.

However, the Excel sort runs significantly faster on sorted, partially sorted, or even roughly ordered data than it does on completely randomized data, whereas it makes little difference to the scripting dictionary. And since it seems quite a lot of Excel data is at least roughly ordered in some way then ...

Add the dictionary object references by opening the project references from the Project menu and adding the Microsoft Scripting Runtime.

http://www.kamath.com/tutorials/images/tut009_vbref.gif

 

[Legacy 14611]

All OK. That works fine.

Interesting coding. Your mimicry of pivot tables is effective.

 

[Dr_bee]

NorthWolves, sorry for the bump on an old post but this is almost exactly what I've been been trying to do for weeks now.

I tried your last bit of code from post #7, no scripting dictionary and it is very close.

What modifications would I need to do three columns, A, B, C and just display a count of the combinations for each ?