A
agarwaldvk
Hi All
Sorry, put this thing in the wrong forum (Programming) earlier!
I have a spreadsheet that contains national data in one workshee
spreading over approximately 35000 rows and 9 columns - no
particularly huge, I do realize that!
This dataset is sorted in to statewise data and this is how is spli
ends up as :-
NSW 10000 rows x 9 columns (Approx)
VIC 10000 rows x 9 columns (Approx)
QLD 10000 rows x 9 columns (Approx)
SA 1000 rows x 9 columns (Approx)
TAS 1000 rows x 9 columns (Approx)
WA 2000 rows x 9 columns (Approx)
NT 500 rows x 9 columns (Approx)
I have named ranges for these data subsets for each of the sates whic
go like so:-
NSW - Search_Range_NSW
VIC - Search_Range_VIC and so on!
There is one sumary worksheet for each state where data is presente
for the quantity of each product (these are approx 75 in number i
total) sold by each of the outlet within the state. For the smalle
states, this is not so much of a hassle but when it comes to the large
3 states viz NSW, VIC and QLD, the recalculation takes far too long
approx. 10+ mins per state - unacceptably long! I tried recalculatio
sheet by sheet but couldn't finish - just too long!
This is the formula that produces the data (quantity of a particula
product sold by a specified outlet) :-
=SUMPRODUCT(ISNUMBER(SEARCH($B3,INDEX(Search_Rang
_NSW,,4),1))*((INDEX(Search_Range_NSW,,6)=$D3))*(
INDEX(Search_Range_NSW,,8)=J$2)+(INDEX(Search_Rang e_NSW,,9)=J$2)))
This formula is copied all the way down to cover all the outlets withi
the state (around 450 rows) and all the way across to cover all th
products (around 75 columns).
In the above formula, the data in column 'D' and 'J' are the produc
codes (these products can be sold either separately or package
together) and column 'B' contains the outlet code. For these large
states, there are about 450 outlets and hence the above formula get
called nearly 450 * 75 = 33750 times - hence taking the time it need
to calculate.
Any suggestions to improve performance.
Does anyone have experience with FastExcel? Would FastExcel be able t
do it better! Their claims at the face of it, look promising! Would i
be worthwhile investing in it??????????? Your experience &/o
recommendations would be greatly appreciated.
Further, is it possible and if yes then would it be better if I ca
calculate this formula for each cell in VBA (since all the use
built-in worksheet formulas in the main formula therein viz INDEX
ISNUMBER, SUMPRODUCT, SEARCH can also be used in VBA) and store th
result in an array and later dump the whole result array (values only
in the appropirate worksheet area?
Best regards
Deepak Agarwa
Sorry, put this thing in the wrong forum (Programming) earlier!
I have a spreadsheet that contains national data in one workshee
spreading over approximately 35000 rows and 9 columns - no
particularly huge, I do realize that!
This dataset is sorted in to statewise data and this is how is spli
ends up as :-
NSW 10000 rows x 9 columns (Approx)
VIC 10000 rows x 9 columns (Approx)
QLD 10000 rows x 9 columns (Approx)
SA 1000 rows x 9 columns (Approx)
TAS 1000 rows x 9 columns (Approx)
WA 2000 rows x 9 columns (Approx)
NT 500 rows x 9 columns (Approx)
I have named ranges for these data subsets for each of the sates whic
go like so:-
NSW - Search_Range_NSW
VIC - Search_Range_VIC and so on!
There is one sumary worksheet for each state where data is presente
for the quantity of each product (these are approx 75 in number i
total) sold by each of the outlet within the state. For the smalle
states, this is not so much of a hassle but when it comes to the large
3 states viz NSW, VIC and QLD, the recalculation takes far too long
approx. 10+ mins per state - unacceptably long! I tried recalculatio
sheet by sheet but couldn't finish - just too long!
This is the formula that produces the data (quantity of a particula
product sold by a specified outlet) :-
=SUMPRODUCT(ISNUMBER(SEARCH($B3,INDEX(Search_Rang
_NSW,,4),1))*((INDEX(Search_Range_NSW,,6)=$D3))*(
INDEX(Search_Range_NSW,,8)=J$2)+(INDEX(Search_Rang e_NSW,,9)=J$2)))
This formula is copied all the way down to cover all the outlets withi
the state (around 450 rows) and all the way across to cover all th
products (around 75 columns).
In the above formula, the data in column 'D' and 'J' are the produc
codes (these products can be sold either separately or package
together) and column 'B' contains the outlet code. For these large
states, there are about 450 outlets and hence the above formula get
called nearly 450 * 75 = 33750 times - hence taking the time it need
to calculate.
Any suggestions to improve performance.
Does anyone have experience with FastExcel? Would FastExcel be able t
do it better! Their claims at the face of it, look promising! Would i
be worthwhile investing in it??????????? Your experience &/o
recommendations would be greatly appreciated.
Further, is it possible and if yes then would it be better if I ca
calculate this formula for each cell in VBA (since all the use
built-in worksheet formulas in the main formula therein viz INDEX
ISNUMBER, SUMPRODUCT, SEARCH can also be used in VBA) and store th
result in an array and later dump the whole result array (values only
in the appropirate worksheet area?
Best regards
Deepak Agarwa