| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Multiple Criteria and Return values positioned above the zero
Hi All,
I am using Windows Excel 2003. If possible, I would like a single and flexible formula to provide the results of the scenario listed below. Scenario: I have a dynamic named range called Data. Data spans 7 columns and many rows. Row 17 is header. Row 18 may have values in some columns. Row 19, column P is start of Data. The oldest data is at the top, starting in row 19. I would like a specific sequence / pattern matched and then 2 values returned to the same cell using a comma to separate them. The paired, matched values should be returned across a single row until there are no more instances to match. The pattern matching is NOT single column based but spans all 7 columns. That is, the criteria to be found must start with the oldest instance of the criteria (working from the top row 19 and down) which can be in any column of Data, and then the 2nd oldest which can again be in any column, 3rd oldest which can again be in any column and so on. PATTERN TO MATCH starting from top of dynamic range Data: Step 1. Any value - Step 2. 0 value this must be on next row below value in Step 1. Step 3. 1st instance of criteria *offset 1 row & using criteria value count down row. Step 4. 0 value this must be on next row below value in Step 3. Start process again Step 1. Any value - Step 2. 0 value this must be on next row below value in Step 1. Step 3. 2nd instance of criteria *offset 1 row & using criteria value count down row. Step 4. 0 value this must be on next row below value in Step 3. Start process again Step 1. Any value - Step 2. 0 value this must be on next row below value in Step 1. Step 3. 3rd instance of criteria *offset 1 row & using criteria value count down row. Step 4. 0 value this must be on next row below value in Step 3. Start process again Step 1. Any value - Step 2. 0 value this must be on next row below value in Step 1. Step 3. 4th instance of criteria *offset 1 row & using criteria value count down row. Step 4. 0 value this must be on next row below value in Step 3. Continue pattern matching process until no more criteria can be found to return across a single row. The 1st instance must be the oldest instance of the criteria starting from the top of my range Data. The 2nd instance is 2nd oldest instance of the criteria. The 3rd instance is the 3rd oldest instance and so forth. * offset 1 row & using criteria value count down row: Once youve counted down x number of rows based on the value of the criteria, the cell that you reach should equal the value of the criteria. Any value: Is literally any value that is found above the 0 value in Step2. In the sample data the criteria Im looking for is 4 (it will vary) and the pattern should be matched as explained above. Sample Data Expected Results: 2,4 3,4 2,4 11,4 3,4 Sample Data Layout: RowNo ColP ColQ ColR ColS ColT ColU ColV 19 1 1 0 1 0 1 1 20 2 2 1 2 1 0 2 21 3 3 2 3 2 1 0 22 4 4 3 0 3 2 1 23 5 5 0 1 0 3 2 24 6 0 0 2 1 4 3 25 7 1 1 3 2 5 4 26 8 0 2 4 0 6 0 27 9 1 3 0 1 7 0 28 10 2 4 1 2 8 1 29 11 3 5 0 3 9 2 30 0 4 6 1 4 10 0 31 1 5 7 2 5 11 1 32 2 6 8 3 6 0 2 33 3 7 9 0 0 1 3 34 4 8 10 1 1 2 4 35 5 0 11 2 2 3 0 36 6 1 12 3 3 4 1 37 7 2 13 0 4 0 2 38 8 3 14 1 5 1 3 39 9 4 15 2 6 2 4 40 0 5 16 3 7 0 5 41 1 6 17 0 8 1 0 42 2 7 18 1 9 2 1 43 3 8 19 2 0 3 2 44 4 9 20 3 1 4 0 45 5 10 21 4 2 5 1 46 6 11 22 0 3 6 2 47 0 12 23 1 4 7 3 48 1 13 24 2 5 8 4 Workings for Expected Results: 1st paired match is Column V, rows 19-26 return 2,4 2nd paired match is Column S, rows 19-27 return 3,4 3rd paired match is Column V, rows 28-35 return 2,4 4th paired match is Column U, rows 21-37 return 11,4 5th paired match is Column S, rows 38-46 return 3,4 NB: Column V, rows 42-48 is NOT a paired match because there is no 0 (zero) below the criteria 4 in row 48. Your help is very much appreciated. Thanks, Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200911/1 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match Multiple Criteria and Return values positioned above the zero
Assumptions...
P19:V48 contains the data (referred to by the dynamic named range 'Data') AA17 contains the criteria Defined Names... Insert Name Define Name: Col Refers to: =INDEX(Data,0,ROWS(AA$19:AA19)) Click Add Name: Array1 Refers to: =OFFSET(Col,,,ROWS(Col)-$AA$17-2) Click Add Name: Array2 Refers to: =N(OFFSET(Col,ROW(INDIRECT("1:"&ROWS(Col)-$AA$17-2))-1,0,1)) Click Add Name: Array3 Refers to: =N(OFFSET(Col,ROW(INDIRECT("1:"&ROWS(Col)-$AA$17-2))+TRANSPOSE(ROW(INDIRE CT("1:"&$AA$17+2))-1),,1)) Click Add Name: Array4 Refers to: =MMULT(IF(Array20,IF(Array3=TRANSPOSE(MOD(ROW(IND IRECT("1:"&$AA$17+2))-1 ,$AA$17+1)),1,0),0),ROW(INDIRECT("1:"&$AA$17+2))^0 ) Click Ok Formulas... AA19, copied down: =SUMPRODUCT(--(Array4=$AA$17+2)) AB19, confirmed with CONTROL+SHIFT+ENTER, copied across and down: =IF(COLUMNS($AB19:AB19)<=$AA19,INDEX(Array1,SMALL( IF(Array4=$AA$17+2,ROW( Array1)-MIN(ROW(Array1))+1),COLUMNS($AB19:AB19))),"") -- Domenic Microsoft Excel MVP www.xl-central.com Your Quick Reference to Excel Solutions In article <9f4f6bab1feb6@uwe, "Sam via OfficeKB.com" <u4102@uwe wrote: Hi All, I am using Windows Excel 2003. If possible, I would like a single and flexible formula to provide the results of the scenario listed below. Scenario: I have a dynamic named range called Data. Data spans 7 columns and many rows. Row 17 is header. Row 18 may have values in some columns. Row 19, column P is start of Data. The oldest data is at the top, starting in row 19. I would like a specific sequence / pattern matched and then 2 values returned to the same cell using a comma to separate them. The paired, matched values should be returned across a single row until there are no more instances to match. The pattern matching is NOT single column based but spans all 7 columns. That is, the criteria to be found must start with the oldest instance of the criteria (working from the top row 19 and down) which can be in any column of Data, and then the 2nd oldest which can again be in any column, 3rd oldest which can again be in any column and so on. PATTERN TO MATCH starting from top of dynamic range Data: Step 1. Any value - Step 2. 0 value this must be on next row below value in Step 1. Step 3. 1st instance of criteria *offset 1 row & using criteria value count down row. Step 4. 0 value this must be on next row below value in Step 3. Start process again Step 1. Any value - Step 2. 0 value this must be on next row below value in Step 1. Step 3. 2nd instance of criteria *offset 1 row & using criteria value count down row. Step 4. 0 value this must be on next row below value in Step 3. Start process again Step 1. Any value - Step 2. 0 value this must be on next row below value in Step 1. Step 3. 3rd instance of criteria *offset 1 row & using criteria value count down row. Step 4. 0 value this must be on next row below value in Step 3. Start process again Step 1. Any value - Step 2. 0 value this must be on next row below value in Step 1. Step 3. 4th instance of criteria *offset 1 row & using criteria value count down row. Step 4. 0 value this must be on next row below value in Step 3. Continue pattern matching process until no more criteria can be found to return across a single row. The 1st instance must be the oldest instance of the criteria starting from the top of my range Data. The 2nd instance is 2nd oldest instance of the criteria. The 3rd instance is the 3rd oldest instance and so forth. * offset 1 row & using criteria value count down row: Once youve counted down x number of rows based on the value of the criteria, the cell that you reach should equal the value of the criteria. Any value: Is literally any value that is found above the 0 value in Step2. In the sample data the criteria Im looking for is 4 (it will vary) and the pattern should be matched as explained above. Sample Data Expected Results: 2,4 3,4 2,4 11,4 3,4 Sample Data Layout: RowNo ColP ColQ ColR ColS ColT ColU ColV 19 1 1 0 1 0 1 1 20 2 2 1 2 1 0 2 21 3 3 2 3 2 1 0 22 4 4 3 0 3 2 1 23 5 5 0 1 0 3 2 24 6 0 0 2 1 4 3 25 7 1 1 3 2 5 4 26 8 0 2 4 0 6 0 27 9 1 3 0 1 7 0 28 10 2 4 1 2 8 1 29 11 3 5 0 3 9 2 30 0 4 6 1 4 10 0 31 1 5 7 2 5 11 1 32 2 6 8 3 6 0 2 33 3 7 9 0 0 1 3 34 4 8 10 1 1 2 4 35 5 0 11 2 2 3 0 36 6 1 12 3 3 4 1 37 7 2 13 0 4 0 2 38 8 3 14 1 5 1 3 39 9 4 15 2 6 2 4 40 0 5 16 3 7 0 5 41 1 6 17 0 8 1 0 42 2 7 18 1 9 2 1 43 3 8 19 2 0 3 2 44 4 9 20 3 1 4 0 45 5 10 21 4 2 5 1 46 6 11 22 0 3 6 2 47 0 12 23 1 4 7 3 48 1 13 24 2 5 8 4 Workings for Expected Results: 1st paired match is Column V, rows 19-26 return 2,4 2nd paired match is Column S, rows 19-27 return 3,4 3rd paired match is Column V, rows 28-35 return 2,4 4th paired match is Column U, rows 21-37 return 11,4 5th paired match is Column S, rows 38-46 return 3,4 NB: Column V, rows 42-48 is NOT a paired match because there is no 0 (zero) below the criteria 4 in row 48. Your help is very much appreciated. Thanks, Sam |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match Multiple Criteria and Return values positioned above the zero
Hi Domenic,
Thank you very much for reply, and all your time and assistance. Your solution does provide an answer in part. The outstanding part is somehow trying to list the returned values from earliest to most recent. I'm still trying to see if I can somehow list the returned values returned from earliest to most recent. Thank you for all your help. If anything else comes to mind very much appreciated. Cheers, Sam Domenic wrote: Assumptions... P19:V48 contains the data (referred to by the dynamic named range 'Data') AA17 contains the criteria Defined Names... Insert Name Define Name: Col Refers to: =INDEX(Data,0,ROWS(AA$19:AA19)) Click Add Name: Array1 Refers to: =OFFSET(Col,,,ROWS(Col)-$AA$17-2) Click Add Name: Array2 Refers to: =N(OFFSET(Col,ROW(INDIRECT("1:"&ROWS(Col)-$AA$17-2))-1,0,1)) Click Add Name: Array3 Refers to: =N(OFFSET(Col,ROW(INDIRECT("1:"&ROWS(Col)-$AA$17-2))+TRANSPOSE(ROW(INDIRE CT("1:"&$AA$17+2))-1),,1)) Click Add Name: Array4 Refers to: =MMULT(IF(Array20,IF(Array3=TRANSPOSE(MOD(ROW(IN DIRECT("1:"&$AA$17+2))-1 ,$AA$17+1)),1,0),0),ROW(INDIRECT("1:"&$AA$17+2))^ 0) Click Ok Formulas... AA19, copied down: =SUMPRODUCT(--(Array4=$AA$17+2)) AB19, confirmed with CONTROL+SHIFT+ENTER, copied across and down: =IF(COLUMNS($AB19:AB19)<=$AA19,INDEX(Array1,SMALL (IF(Array4=$AA$17+2,ROW( Array1)-MIN(ROW(Array1))+1),COLUMNS($AB19:AB19))),"") Hi All, [quoted text clipped - 123 lines] Thanks, Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200911/1 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match Multiple Criteria and Return values positioned above the zero
The outstanding part is
somehow trying to list the returned values from earliest to most recent. Somewhat unclear... Can you provide the expected result for the sample data? -- Domenic Microsoft Excel MVP www.xl-central.com Your Quick Reference to Excel Solutions In article <9f9c78ba85f1e@uwe, "Sam via OfficeKB.com" <u4102@uwe wrote: Hi Domenic, Thank you very much for reply, and all your time and assistance. Your solution does provide an answer in part. The outstanding part is somehow trying to list the returned values from earliest to most recent. I'm still trying to see if I can somehow list the returned values returned from earliest to most recent. Thank you for all your help. If anything else comes to mind very much appreciated. Cheers, Sam Domenic wrote: Assumptions... P19:V48 contains the data (referred to by the dynamic named range 'Data') AA17 contains the criteria Defined Names... Insert Name Define Name: Col Refers to: =INDEX(Data,0,ROWS(AA$19:AA19)) Click Add Name: Array1 Refers to: =OFFSET(Col,,,ROWS(Col)-$AA$17-2) Click Add Name: Array2 Refers to: =N(OFFSET(Col,ROW(INDIRECT("1:"&ROWS(Col)-$AA$17-2))-1,0,1)) Click Add Name: Array3 Refers to: =N(OFFSET(Col,ROW(INDIRECT("1:"&ROWS(Col)-$AA$17-2))+TRANSPOSE(ROW(INDIRE CT("1:"&$AA$17+2))-1),,1)) Click Add Name: Array4 Refers to: =MMULT(IF(Array20,IF(Array3=TRANSPOSE(MOD(ROW(IN DIRECT("1:"&$AA$17+2))-1 ,$AA$17+1)),1,0),0),ROW(INDIRECT("1:"&$AA$17+2))^ 0) Click Ok Formulas... AA19, copied down: =SUMPRODUCT(--(Array4=$AA$17+2)) AB19, confirmed with CONTROL+SHIFT+ENTER, copied across and down: =IF(COLUMNS($AB19:AB19)<=$AA19,INDEX(Array1,SMALL (IF(Array4=$AA$17+2,ROW( Array1)-MIN(ROW(Array1))+1),COLUMNS($AB19:AB19))),"") Hi All, [quoted text clipped - 123 lines] Thanks, Sam |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match Multiple Criteria and Return values positioned above the zero
Hi Domenic,
Thank you for reply. Domenic wrote: The outstanding part is somehow trying to list the returned values from earliest to most recent. Somewhat unclear... Can you provide the expected result for the sample data? Sample Data Expected Results: 2,4 3,4 2,4 11,4 3,4 Using the row numbers to ascertain oldest and most recent criteria data. The main part of the solution is to list the expected results in order of the earliest (oldest) result to the most recent. I thought about somehow using the row number after each criteria value; that would be the row below the criteria where the 0 resides. Therefore, per the sample data, we would be looking at row numbers 27, 46, 37, 26, and 35. I would then use those row numbers in ascending order to order the returned values. However, using your formula if, I inserted columns before or rows beneath each returned value, it would interfere with the fill across and fill down of the formula. So I cant see a way to do that. Workings for Expected Results: 1st paired match is Column V, rows 19-26 return 2,4 2nd paired match is Column S, rows 19-27 return 3,4 3rd paired match is Column V, rows 28-35 return 2,4 4th paired match is Column U, rows 21-37 return 11,4 5th paired match is Column S, rows 38-46 return 3,4 NB: Column V, rows 42-48 is NOT a paired match because there is no 0 (zero) below the criteria 4 in row 48. Your help is very much appreciated. Thanks, Sam -- Message posted via http://www.officekb.com |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match Multiple Criteria and Return values positioned above the zero
First, replace the formula for AB19 with the following...
AB19, confirmed with CONTROL+SHIFT+ENTER, copied across and down: =IF(COLUMNS($AB19:AB19)<=$AA19,SMALL(IF(Array4=$AA $17+2,ROW(Array1)-MIN(R OW(Array1))+1),COLUMNS($AB19:AB19)),"") Then try... AA28: =SUM(AA19:AA25) AB28, confirmed with CONTROL+SHIFT+ENTER, and copied across: =IF(COLUMNS($AB28:AB28)<=$AA28,INDEX(Data,SMALL($A B$19:$AK$25,COLUMNS($AB 28:AB28)),RIGHT(SMALL(IF($AB$19:$AK$25<"",$AB$19: $AK$25*10^7+ROW($AB$19: $AK$25)-ROW($AB$19)+1),COLUMNS($AB28:AB28)),7))&","&$AA$17 ,"") -- Domenic Microsoft Excel MVP www.xl-central.com Your Quick Reference to Excel Solutions In article <9fa7dc6e46aad@uwe, "Sam via OfficeKB.com" <u4102@uwe wrote: Hi Domenic, Thank you for reply. Domenic wrote: The outstanding part is somehow trying to list the returned values from earliest to most recent. Somewhat unclear... Can you provide the expected result for the sample data? Sample Data Expected Results: 2,4 3,4 2,4 11,4 3,4 Using the row numbers to ascertain oldest and most recent criteria data. The main part of the solution is to list the expected results in order of the earliest (oldest) result to the most recent. I thought about somehow using the row number after each criteria value; that would be the row below the criteria where the 0 resides. Therefore, per the sample data, we would be looking at row numbers 27, 46, 37, 26, and 35. I would then use those row numbers in ascending order to order the returned values. However, using your formula if, I inserted columns before or rows beneath each returned value, it would interfere with the fill across and fill down of the formula. So I cant see a way to do that. Workings for Expected Results: 1st paired match is Column V, rows 19-26 return 2,4 2nd paired match is Column S, rows 19-27 return 3,4 3rd paired match is Column V, rows 28-35 return 2,4 4th paired match is Column U, rows 21-37 return 11,4 5th paired match is Column S, rows 38-46 return 3,4 NB: Column V, rows 42-48 is NOT a paired match because there is no 0 (zero) below the criteria 4 in row 48. Your help is very much appreciated. Thanks, Sam |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find a Match in Multiple Places & Return Multiple Values | Excel Worksheet Functions | |||
| Match Multiple Criteria & Return Numeric Labels across single row | Excel Worksheet Functions | |||
| MATCH Multiple Criteria & Return Previous / Penultimate Match | Excel Worksheet Functions | |||
| Match Single Numeric Criteria and Return Multiple Numeric Labels | Excel Worksheet Functions | |||
| match multiple criteria & return value from array | Excel Worksheet Functions | |||
Hybrid Mode
