Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multi Manufactured Products | Excel Discussion (Misc queries) | |||
Metering Office 97 products | Excel Discussion (Misc queries) |