Alternative solution needed for slow calculating Array formulas !

[fisht]

Hi,

I am currently calculating ~20,000 array formulas to complete the below task, however it takes way too long to complete and often times freezes my excel (excel 2010). So I am hoping that someone can help me out and determine a faster solution (possibly with VBA) that can complete this task in a fraction of the time! Below are the details of the workbook I am currently using:

Workbook Info

'Data' tab - includes a data dump starting in column 'A:N'-- maximum number of rows the data dump will contain is 10,000. Company names are listed in column 'B', dates are listed in column 'F', and discount & promotion types are listed in column 'L'.​

​

'Company 1' tab - 3 months of data organized by the following:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Rows[/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]....[/TD]
[TD]Column AF[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Company 1 NAME[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]August Dates[/TD]
[TD]8/1/2014[/TD]
[TD]8/2/2014[/TD]
[TD]....[/TD]
[TD]8/31/2014[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]D&P Type 1[/TD]
[TD]array formula[/TD]
[TD]array formula[/TD]
[TD][/TD]
[TD]array formula[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]D&P Type 2[/TD]
[TD]array formula[/TD]
[TD]array formula[/TD]
[TD][/TD]
[TD]array formula[/TD]
[/TR]
[TR]
[TD].....[/TD]
[TD].....[/TD]
[TD]array formula[/TD]
[TD]array formula[/TD]
[TD][/TD]
[TD]array formula[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]D&P Type 20[/TD]
[TD]array formula[/TD]
[TD]array formula[/TD]
[TD][/TD]
[TD]array formula[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 500"]
<tbody>[TR]
[TD]Rows[/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]....[/TD]
[TD]Column AF[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]September Dates[/TD]
[TD]9/1/2014[/TD]
[TD]9/2/2014[/TD]
[TD]....[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]D&P Type 1[/TD]
[TD]array formula[/TD]
[TD]array formula[/TD]
[TD][/TD]
[TD]array formula[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]D&P Type 2[/TD]
[TD]array formula[/TD]
[TD]array formula[/TD]
[TD][/TD]
[TD]array formula[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]D&P Type 3[/TD]
[TD]array formula[/TD]
[TD]array formula[/TD]
[TD][/TD]
[TD]array formula[/TD]
[/TR]
[TR]
[TD].....[/TD]
[TD][/TD]
[TD]array formula[/TD]
[TD]array formula[/TD]
[TD][/TD]
[TD]array formula[/TD]
[/TR]
[TR]
[TD]45[/TD]
[TD]D&P Type 20[/TD]
[TD]array formula[/TD]
[TD]array formula[/TD]
[TD][/TD]
[TD]array formula[/TD]
[/TR]
</tbody>[/TABLE]
*** There are only 30 days in September, so therefore the last day of the month ends in column 'AE'.​

Month 3- same as above except dates for October 2014 are in row '48' and D&P types are between rows '49:68'.


'Company 2' tab - same as above, with the exception of a different company name in cell $A$1 of this worksheet.

Worksheets continue to 'Company 10' -- these calculations are run for 10 different companies, which have their own worksheets, and each worksheet shares the identical structure as listed above.

Calculation criteria (for each company worksheet)

Example calculation in cell 'B3'​

1. 
   [*=1]Take company name in cell '$A$1' and date in cell 'B$2' and match each criteria to columns 'B' and 'F' (respectively) on the 'Data' tab.
   [*=1]Then return multiple 'D&P type' values from column 'L' on the 'Data' tab that match the criteria from #1.
   [*=1]There will be a maximum of 20 'D&P types' that run on any given date for each company. (which is why I have 20 rows for 'D&P types' for each day).

I need these multiple 'D&P type' values returned to each day, for each of the 3 months, and for each of the 10 companies.

Please let me know if I can help clarify anything and your help will be very much appreciated and will save SO much time!!

Thank you in advance!​

 

[cyrilbrd]

Hi just being curious, since yo have Excel 2010, have you tried Power Pivot? Suggest you post as well a query in the power pivot forum with a link to this thread.
Power Pivot would eliminate your "speed" concern as your raw data doesn't seem to be that big. Further more those computation in Columns B:AF might be solved within Power Pivot itself (similar to the calculated field of a Pivot but with far more leeway. For your perusal.

 

[fisht]

Hi cyrilbrd- thanks for the recommendation, however I am trying to avoid using pivot tables.

Any ideas for a different solution?

 

[fisht]

Anyone else know of another solution? Your help will be very much appreciated!

Also, is my explanation clear enough?

Thank you!