Hi All


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_Range _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.


Best regards


Deepak Agarwa

--
agarwaldv
-----------------------------------------------------------------------
agarwaldvk's Profile: http://www.excelforum.com/member.php...fo&userid=1134
View this thread: http://www.excelforum.com/showthread.php?threadid=27232

Is the layout such that you can use a Pivot Table?

On Mon, 25 Oct 2004 20:51:42 -0500, agarwaldvk
wrote:


Hi All


I have a spreadsheet that contains national data in one worksheet
spreading over approximately 35000 rows and 9 columns - not
particularly huge, I do realize that!

This dataset is sorted in to statewise data and this is how is split
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 which
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 presented
for the quantity of each product (these are approx 75 in number in
total) sold by each of the outlet within the state. For the smaller
states, this is not so much of a hassle but when it comes to the larger
3 states viz NSW, VIC and QLD, the recalculation takes far too long -
approx. 10+ mins per state - unacceptably long! I tried recalculation
sheet by sheet but couldn't finish - just too long!

This is the formula that produces the data (quantity of a particular
product sold by a specified outlet) :-

=SUMPRODUCT(ISNUMBER(SEARCH($B3,INDEX(Search_Rang e_NSW,,4),1))*((INDEX(Search_Range_NSW,,6)=$D3))*( (INDEX(Search_Range_NSW,,8)=J$2)+(INDEX(Search_Ran ge_NSW,,9)=J$2)))

This formula is copied all the way down to cover all the outlets within
the state (around 450 rows) and all the way across to cover all the
products (around 75 columns).

In the above formula, the data in column 'D' and 'J' are the product
codes (these products can be sold either separately or packages
together) and column 'B' contains the outlet code. For these larger
states, there are about 450 outlets and hence the above formula gets
called nearly 450 * 75 = 33750 times - hence taking the time it needs
to calculate.

Any suggestions to improve performance.

Does anyone have experience with FastExcel? Would FastExcel be able to
do it better! Their claims at the face of it, look promising! Would it
be worthwhile investing in it??????????? Your experience &/or
recommendations would be greatly appreciated.


Best regards


Deepak Agarwal