better/faster way than sum products?
 

Hello,

I got some cool recommendations from this group for setting up some
weekly stats I do in Excel. I've pushed this to the limits and wanted
to illicit some advise on moving forward and doing this a better way.

I have one sheet worksheet (called Sheet3) with the header ROW that's
20 across. Each header cell is a particular look up value.
Then, I have about 600 rows which the 1st cell is another look up
value.
Each of the cells then has the below sum product:
=SUMPRODUCT(((Sheet2!$A$1:$A$7570)=LOWER(Sheet3!$A 2))*((Sheet2!$B$1:$B$7570)=Sheet3!Q$1)*((Sheet2!$D $1:$D$7570)="Yes"))
(so, look up the values in Sheet2 that comprise of values in 3 columns)

Here's where I run into a problem.
The 2nd sheet which contains the lookup values is quite big.
It has 7570 rows.

So, for each cell in my 600*20 worksheet I'm then doing a 7570*3
lookups (and then all the sum products inside)

This doesn't seem very efficient and pegs my (newish) system for a good
15 or 20 minutes.
In addition, it makes debugging impossible using the "Evaluate Formula"
tool.

The look up worksheet is the output of a pivot table and is generated
by some auditing system which I'm not privy to so I'm stuck with that.


I could write something up with Perl to parse the data but (call me
crazy) I'm kinda enjoying learning a bit more about Excel.
Any suggestions on improving this?

Many Thanks,
Alex

Hi
I would consider using a pivot table instead

"alex" wrote:

Hello,

I got some cool recommendations from this group for setting up some
weekly stats I do in Excel. I've pushed this to the limits and wanted
to illicit some advise on moving forward and doing this a better way.

I have one sheet worksheet (called Sheet3) with the header ROW that's
20 across. Each header cell is a particular look up value.
Then, I have about 600 rows which the 1st cell is another look up
value.
Each of the cells then has the below sum product:
=SUMPRODUCT(((Sheet2!$A$1:$A$7570)=LOWER(Sheet3!$A 2))*((Sheet2!$B$1:$B$7570)=Sheet3!Q$1)*((Sheet2!$D $1:$D$7570)="Yes"))
(so, look up the values in Sheet2 that comprise of values in 3 columns)

Here's where I run into a problem.
The 2nd sheet which contains the lookup values is quite big.
It has 7570 rows.

So, for each cell in my 600*20 worksheet I'm then doing a 7570*3
lookups (and then all the sum products inside)

This doesn't seem very efficient and pegs my (newish) system for a good
15 or 20 minutes.
In addition, it makes debugging impossible using the "Evaluate Formula"
tool.

The look up worksheet is the output of a pivot table and is generated
by some auditing system which I'm not privy to so I'm stuck with that.


I could write something up with Perl to parse the data but (call me
crazy) I'm kinda enjoying learning a bit more about Excel.
Any suggestions on improving this?

Many Thanks,
Alex

Hmmm....
Ok, thanks.
I've read up a bit on pivot tables and that's way faster
(but presents a different problem)
I've posted a follow up question:
http://groups-beta.google.com/group/...4526db37 0e0f
(i hope that link works)
-Alex