Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MATCH using multiple criteria?
Is there a way to find the first row in a data set that meets multiple
criteria? Something that works like =MATCH(Criteria,Array,0) except for multiple criteria in multiple columns. I would also like to know if there is a way to find the LAST row that meets these criteria. I am using SUMPRODUCT to count and sum in a large database using predefined ranges. I am trying to to speed up calculation by limiting the defined ranges to include only the applicable rows, instead of the entire database. I'm using 5 conditions that the user defines with drop down boxes. The conditions for the date range include = and <= in the sumproduct functions. I can do this by using an extra column: =IF(AND("condition1 is met","condition2 is met",etc),="cell above"+1,="cell above") copy down, then =MATCH(1,"extra column",0) gives the first row =MATCH(MAX("extra column"),"extra column",0) gives the last row However, even though 60,000 of these IF statements (I have 10,000 rows and 6 user defined groups, time periods, etc in the report) only take 3 seconds to calculate on their own (using calcualte worksheet), the entire report takes about 20% longer to recalculate. We can live with it for now, but we keep adding data. If the answer is no, and this won't work to speed up calculation, that's okay. Please tell if that is because there is no better way to match on multiple criteria, or because it will slow the calculation anyway.. Thanks for any help you can offer. Bob |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
MATCH using multiple criteria?
I'm not sure if it would actually speed things up for you but you could find the first match for 2 conditions something like this =MATCH(1,INDEX((A1:A1000="apple")*(B1:B1000="banan a"),0),0) and the last with =MATCH(2,INDEX(1/(A1:A1000="apple")*(B1:B1000="banana"),0)) you can add more conditions quite easily -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=507249 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
MATCH using multiple criteria?
Looks like an array formula - don't think you can speed up an array formula
by using another array formula to make the first array formula do less work. If you don't think sumproduct is an array formula, think again. you can add more conditions quite easily And more processing time <g -- Regards. Tom Ogilvy "daddylonglegs" wrote in message news:daddylonglegs.22k59n_1138809305.0206@excelfor um-nospam.com... I'm not sure if it would actually speed things up for you but you could find the first match for 2 conditions something like this =MATCH(1,INDEX((A1:A1000="apple")*(B1:B1000="banan a"),0),0) and the last with =MATCH(2,INDEX(1/(A1:A1000="apple")*(B1:B1000="banana"),0)) you can add more conditions quite easily -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=507249 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
MATCH using multiple criteria?
THANKS
That cut the calculation time for a specific report from 105 seconds to 11 seconds!!!! I guess you're my daddy now I had to add a set of parenthesis after the 1/ and before the next comma: =MATCH(2,INDEX(1/((A1:A1000="apple")*(B1:B1000="banana")),0)) even though this should not make a difference (maybe because I'm using 5 criteria instead of 2?). Without the extra parenthesis, I keep getting the last row of data instead of the last that met the conditions. The formula for the first row works without the extra parenthesis. Go figure. "daddylonglegs" wrote in message news:daddylonglegs.22k59n_1138809305.0206@excelfor um-nospam.com... I'm not sure if it would actually speed things up for you but you could find the first match for 2 conditions something like this =MATCH(1,INDEX((A1:A1000="apple")*(B1:B1000="banan a"),0),0) and the last with =MATCH(2,INDEX(1/(A1:A1000="apple")*(B1:B1000="banana"),0)) you can add more conditions quite easily -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=507249 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
MATCH using multiple criteria?
It increases the speed by reducing the size of the arrays that each
sumproduct formula calculates. I certainly get what you're saying, but I'm using about 250 sumproduct formulas for each of 6 groups (1500 total) with 5 criteria each. Adding 12 formulas to minimize the range for the sumproduct formulas makes little difference in the worst case scenario, and a lot of difference in the best. I have 10,000+ rows of data (so far). If I want to compare 6 time periods, the sumproduct formulas only have to look at about 1,7000 rows for each time period, instead each one looking at 10,000 rows. Of course I have to keep the data sorted in a logical way to best reduce the number of rows in each group. For example, the user could define the report to compare 6 salespeople over the the entire time, in which case the speed is not increased unless the data is sorted on salesperson. However, there is usually a limited time period specified for each group, so as long as the data is sorted on time period (quarter), region, and class of customer, the speed for most desired reports will be improved. "Tom Ogilvy" wrote in message ... Looks like an array formula - don't think you can speed up an array formula by using another array formula to make the first array formula do less work. If you don't think sumproduct is an array formula, think again. you can add more conditions quite easily And more processing time <g -- Regards. Tom Ogilvy "daddylonglegs" wrote in message news:daddylonglegs.22k59n_1138809305.0206@excelfor um-nospam.com... I'm not sure if it would actually speed things up for you but you could find the first match for 2 conditions something like this =MATCH(1,INDEX((A1:A1000="apple")*(B1:B1000="banan a"),0),0) and the last with =MATCH(2,INDEX(1/(A1:A1000="apple")*(B1:B1000="banana"),0)) you can add more conditions quite easily -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=507249 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
MATCH using multiple criteria?
Different visualizations of what you were asking. It appears you have set
aside a separate area to define your ranges once rather than embed such logic in each sumproduct formula - which is what I understood you wanted to do. -- Regards, Tom Ogilvy "Bob Tarburton" wrote in message ... It increases the speed by reducing the size of the arrays that each sumproduct formula calculates. I certainly get what you're saying, but I'm using about 250 sumproduct formulas for each of 6 groups (1500 total) with 5 criteria each. Adding 12 formulas to minimize the range for the sumproduct formulas makes little difference in the worst case scenario, and a lot of difference in the best. I have 10,000+ rows of data (so far). If I want to compare 6 time periods, the sumproduct formulas only have to look at about 1,7000 rows for each time period, instead each one looking at 10,000 rows. Of course I have to keep the data sorted in a logical way to best reduce the number of rows in each group. For example, the user could define the report to compare 6 salespeople over the the entire time, in which case the speed is not increased unless the data is sorted on salesperson. However, there is usually a limited time period specified for each group, so as long as the data is sorted on time period (quarter), region, and class of customer, the speed for most desired reports will be improved. "Tom Ogilvy" wrote in message ... Looks like an array formula - don't think you can speed up an array formula by using another array formula to make the first array formula do less work. If you don't think sumproduct is an array formula, think again. you can add more conditions quite easily And more processing time <g -- Regards. Tom Ogilvy "daddylonglegs" wrote in message news:daddylonglegs.22k59n_1138809305.0206@excelfor um-nospam.com... I'm not sure if it would actually speed things up for you but you could find the first match for 2 conditions something like this =MATCH(1,INDEX((A1:A1000="apple")*(B1:B1000="banan a"),0),0) and the last with =MATCH(2,INDEX(1/(A1:A1000="apple")*(B1:B1000="banana"),0)) you can add more conditions quite easily -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=507249 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
MATCH using multiple criteria?
That's right, and thanks for help on this and previous, you always help me
visualize what I'm doing right and/or wrong. "Tom Ogilvy" wrote in message ... Different visualizations of what you were asking. It appears you have set aside a separate area to define your ranges once rather than embed such logic in each sumproduct formula - which is what I understood you wanted to do. -- Regards, Tom Ogilvy "Bob Tarburton" wrote in message ... It increases the speed by reducing the size of the arrays that each sumproduct formula calculates. I certainly get what you're saying, but I'm using about 250 sumproduct formulas for each of 6 groups (1500 total) with 5 criteria each. Adding 12 formulas to minimize the range for the sumproduct formulas makes little difference in the worst case scenario, and a lot of difference in the best. I have 10,000+ rows of data (so far). If I want to compare 6 time periods, the sumproduct formulas only have to look at about 1,7000 rows for each time period, instead each one looking at 10,000 rows. Of course I have to keep the data sorted in a logical way to best reduce the number of rows in each group. For example, the user could define the report to compare 6 salespeople over the the entire time, in which case the speed is not increased unless the data is sorted on salesperson. However, there is usually a limited time period specified for each group, so as long as the data is sorted on time period (quarter), region, and class of customer, the speed for most desired reports will be improved. "Tom Ogilvy" wrote in message ... Looks like an array formula - don't think you can speed up an array formula by using another array formula to make the first array formula do less work. If you don't think sumproduct is an array formula, think again. you can add more conditions quite easily And more processing time <g -- Regards. Tom Ogilvy "daddylonglegs" wrote in message news:daddylonglegs.22k59n_1138809305.0206@excelfor um-nospam.com... I'm not sure if it would actually speed things up for you but you could find the first match for 2 conditions something like this =MATCH(1,INDEX((A1:A1000="apple")*(B1:B1000="banan a"),0),0) and the last with =MATCH(2,INDEX(1/(A1:A1000="apple")*(B1:B1000="banana"),0)) you can add more conditions quite easily -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=507249 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match / Index multiple criteria return multiple results | Excel Worksheet Functions | |||
MATCH Multiple Criteria & Return Previous / Penultimate Match | Excel Worksheet Functions | |||
match multiple criteria ina range from multiple criteria multiplet | Excel Worksheet Functions | |||
Index & Match functions - multiple criteria and multiple results | Excel Worksheet Functions | |||
Using Match with multiple criteria | Excel Worksheet Functions |