Pivot Table Query

[SiriusProjects]

Hi All,

I have written a VBA application which applies a number of SUMIF formulas to various columns in my dataset. The dataset currently holds 50000 rows and grows larger each month.

Because of this the calculation time is getting longer and longer. At present it takes about 5 minutes.

Having done some testing today I have found that if I create a pivot table from the data and reference that instead, this speeds things up quite considerably.

My question though is can a pivot table be created in memory and referenced from there? I don't need it to be physically on a worksheet as it would only be required temporarily just so that I can reference the row totals. Is this possible or do I definitely have to create it physically?

Thanks.

[CK76]

Pivot table is an object and you'll need it in workbook.

Depending on version of Excel, you may have access to PowreQuery. Which you can use to group data and return total row resulting data.

There are other methods, but without sample workbook, bit hard to give you specifics.

To upload sample, use "Go Advanced" button, and follow "Manage Attachments" hyperlink. It will launch new tab/window for managing uploads.