Crosstab in memory

[Guest]

Greetings and TIA for your time.
I'm summarising a spreadsheet database using a crosstab. I started off
using sumproduct worksheet formulas, but found the recalculation to be very
slow. Next I tried concatenating fields in the database and using countif in
the crosstab. This improved the worksheet calculation time up by a factor of
more than 10. Next i wrote some code to transfer ranges to VBA arrays,
operate on VBA arrays using worksheetfunction.sumproduct then transfer the
result back to the spreadsheet crosstab.
The code worked ok but again there was a performance problem.... which
brings me to my question:
Could I get a performance advantage by concatenating ranges into aVBA array
and operating on the array with VBA functions and then transferring the
resulting array back to the spreadsheet crosstab? If so please guide me on
the best way to concatenate the arrays and which function(s) to use.

 

[Myrna Larson]

With Excel, I expect the best performance would be to write the VBA code to
create a Pivot Table. Turn on the macro recorder and do it manually, then
modify the code as needed.

It could possible be faster in Access, depending on what sort of summary
fields you are creating.