Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Criteria & Return Sequential Count
Hi All,
I would like a formula that can return a numeric sequential count down a single column based on content of another column. The count will be sequential until it meets all the criteria, wherein a zero (0) should be returned and the count reset, starting at one (1), until the criteria is again met. Match criteria in this order: 1) match specific numeric value (input cell for varying criterion); 106. 2) match specific text value, the letter "C" (static criterion); in row directly below 106. 3) match the same numeric value as in (1) above:106; but this must be exactly 106 rows after matching "C" in (2) above. 4) match specific text value, the letter "C" (static criterion); in row directly below the second 106. Criteria met, I would like the value zero (0) to be returned to the cell that houses the second instance of the text value, letter "C". The data starts in row 4, column "BR" holds numeric and text data. The sequential count should be returned down column "BS". Sample Data: col BS BR row4 101 1 row5 102 2 row6 103 3 row7 104 4 row8 C 5 row9 101 6 row10 102 7 row11 103 8 row12 104 9 row13 105 10 row14 106 11 row15 C 12 row16 101 13 row17 102 14 row18 103 15 row19 104 16 row20 105 17 row21 106 18 row22 107 19 row23 108 20 row24 109 21 row25 110 22 row26 C 23 row27 101 24 row28 102 25 row29 103 26 row30 104 27 row31 105 28 row32 106 29 row33 C 30 row34 101 31 row35 102 32 row36 103 33 row37 104 34 row38 105 35 row39 106 36 row40 C 0 row41 101 1 row42 102 2 Expected Results: Column "BR", Row 40 = 0 Looking for numeric value 106 with "C" on the row below = row 33 and then the same number of rows (106) directly below the first "C", with a "C" on the next row = row 40. The sequential count should return zero (0) in column "BR" row 40. Thanks Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200802/1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Criteria & Return Sequential Count
Sam,
Please explain again why the count reset to zero in Row 40 Mike "Sam via OfficeKB.com" wrote: Hi All, I would like a formula that can return a numeric sequential count down a single column based on content of another column. The count will be sequential until it meets all the criteria, wherein a zero (0) should be returned and the count reset, starting at one (1), until the criteria is again met. Match criteria in this order: 1) match specific numeric value (input cell for varying criterion); 106. 2) match specific text value, the letter "C" (static criterion); in row directly below 106. 3) match the same numeric value as in (1) above:106; but this must be exactly 106 rows after matching "C" in (2) above. 4) match specific text value, the letter "C" (static criterion); in row directly below the second 106. Criteria met, I would like the value zero (0) to be returned to the cell that houses the second instance of the text value, letter "C". The data starts in row 4, column "BR" holds numeric and text data. The sequential count should be returned down column "BS". Sample Data: col BS BR row4 101 1 row5 102 2 row6 103 3 row7 104 4 row8 C 5 row9 101 6 row10 102 7 row11 103 8 row12 104 9 row13 105 10 row14 106 11 row15 C 12 row16 101 13 row17 102 14 row18 103 15 row19 104 16 row20 105 17 row21 106 18 row22 107 19 row23 108 20 row24 109 21 row25 110 22 row26 C 23 row27 101 24 row28 102 25 row29 103 26 row30 104 27 row31 105 28 row32 106 29 row33 C 30 row34 101 31 row35 102 32 row36 103 33 row37 104 34 row38 105 35 row39 106 36 row40 C 0 row41 101 1 row42 102 2 Expected Results: Column "BR", Row 40 = 0 Looking for numeric value 106 with "C" on the row below = row 33 and then the same number of rows (106) directly below the first "C", with a "C" on the next row = row 40. The sequential count should return zero (0) in column "BR" row 40. Thanks Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200802/1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Criteria & Return Sequential Count
Hi Mike,
Thanks for reply. The zero in row 40 represents the match of all criteria in the order stated. The count should reset to 1 in the row after the zero (0) when all criteria is met. Cheers, Sam Mike H wrote: Sam, Please explain again why the count reset to zero in Row 40 Mike -- Message posted via http://www.officekb.com |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Criteria & Return Sequential Count
Sam,
I think Iv'e got it Find a variable number x in column A When found there must be a C in the row below Match the variable number x rows above the C The second variable number must also have a C in the row below based upon your data any solution looking for a number higher than 3 will fail because you can't search more than 3 rows back above Row 4 so to get around this instead of looking back x rows as you ask my formula looks back only 8 rows and I start in Row 16 to give some breating space.. My variable number is in D1 When you resolve the above problem you can change the formula change -8 to D1*-1 change -7 to (D1*-1)+1 =IF(AND(A16="C",A15=$D$1,OFFSET(A16,-8,0)=$D$1,OFFSET(A16,-7,0)="C"),0,C15+1) Mike "Sam via OfficeKB.com" wrote: Hi All, I would like a formula that can return a numeric sequential count down a single column based on content of another column. The count will be sequential until it meets all the criteria, wherein a zero (0) should be returned and the count reset, starting at one (1), until the criteria is again met. Match criteria in this order: 1) match specific numeric value (input cell for varying criterion); 106. 2) match specific text value, the letter "C" (static criterion); in row directly below 106. 3) match the same numeric value as in (1) above:106; but this must be exactly 106 rows after matching "C" in (2) above. 4) match specific text value, the letter "C" (static criterion); in row directly below the second 106. Criteria met, I would like the value zero (0) to be returned to the cell that houses the second instance of the text value, letter "C". The data starts in row 4, column "BR" holds numeric and text data. The sequential count should be returned down column "BS". Sample Data: col BS BR row4 101 1 row5 102 2 row6 103 3 row7 104 4 row8 C 5 row9 101 6 row10 102 7 row11 103 8 row12 104 9 row13 105 10 row14 106 11 row15 C 12 row16 101 13 row17 102 14 row18 103 15 row19 104 16 row20 105 17 row21 106 18 row22 107 19 row23 108 20 row24 109 21 row25 110 22 row26 C 23 row27 101 24 row28 102 25 row29 103 26 row30 104 27 row31 105 28 row32 106 29 row33 C 30 row34 101 31 row35 102 32 row36 103 33 row37 104 34 row38 105 35 row39 106 36 row40 C 0 row41 101 1 row42 102 2 Expected Results: Column "BR", Row 40 = 0 Looking for numeric value 106 with "C" on the row below = row 33 and then the same number of rows (106) directly below the first "C", with a "C" on the next row = row 40. The sequential count should return zero (0) in column "BR" row 40. Thanks Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200802/1 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Criteria & Return Sequential Count
I should add C15 must be blank or 0
"Mike H" wrote: Sam, I think Iv'e got it Find a variable number x in column A When found there must be a C in the row below Match the variable number x rows above the C The second variable number must also have a C in the row below based upon your data any solution looking for a number higher than 3 will fail because you can't search more than 3 rows back above Row 4 so to get around this instead of looking back x rows as you ask my formula looks back only 8 rows and I start in Row 16 to give some breating space.. My variable number is in D1 When you resolve the above problem you can change the formula change -8 to D1*-1 change -7 to (D1*-1)+1 =IF(AND(A16="C",A15=$D$1,OFFSET(A16,-8,0)=$D$1,OFFSET(A16,-7,0)="C"),0,C15+1) Mike "Sam via OfficeKB.com" wrote: Hi All, I would like a formula that can return a numeric sequential count down a single column based on content of another column. The count will be sequential until it meets all the criteria, wherein a zero (0) should be returned and the count reset, starting at one (1), until the criteria is again met. Match criteria in this order: 1) match specific numeric value (input cell for varying criterion); 106. 2) match specific text value, the letter "C" (static criterion); in row directly below 106. 3) match the same numeric value as in (1) above:106; but this must be exactly 106 rows after matching "C" in (2) above. 4) match specific text value, the letter "C" (static criterion); in row directly below the second 106. Criteria met, I would like the value zero (0) to be returned to the cell that houses the second instance of the text value, letter "C". The data starts in row 4, column "BR" holds numeric and text data. The sequential count should be returned down column "BS". Sample Data: col BS BR row4 101 1 row5 102 2 row6 103 3 row7 104 4 row8 C 5 row9 101 6 row10 102 7 row11 103 8 row12 104 9 row13 105 10 row14 106 11 row15 C 12 row16 101 13 row17 102 14 row18 103 15 row19 104 16 row20 105 17 row21 106 18 row22 107 19 row23 108 20 row24 109 21 row25 110 22 row26 C 23 row27 101 24 row28 102 25 row29 103 26 row30 104 27 row31 105 28 row32 106 29 row33 C 30 row34 101 31 row35 102 32 row36 103 33 row37 104 34 row38 105 35 row39 106 36 row40 C 0 row41 101 1 row42 102 2 Expected Results: Column "BR", Row 40 = 0 Looking for numeric value 106 with "C" on the row below = row 33 and then the same number of rows (106) directly below the first "C", with a "C" on the next row = row 40. The sequential count should return zero (0) in column "BR" row 40. Thanks Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200802/1 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Criteria & Return Sequential Count
Hi Mike,
Many thanks for assistance. Still trying to find a workaround to layout point raised below. Could the MATCH function assist in finding the 1st leg of the variable value and then use OFFSET for the 2nd leg of variable value. Mike H wrote: Sam, I think Iv'e got it Find a variable number x in column A When found there must be a C in the row below Match the variable number x rows above the C The second variable number must also have a C in the row below Yes based upon your data any solution looking for a number higher than 3 will fail because you can't search more than 3 rows back above Row 4 so to get around this instead of looking back x rows as you ask my formula looks back only 8 rows and I start in Row 16 to give some breating space.. My variable number is in D1 Trying to find a workaround. Stuck with the data layout, starting in row 4. When you resolve the above problem you can change the formula change -8 to D1*-1 change -7 to (D1*-1)+1 =IF(AND(A16="C",A15=$D$1,OFFSET(A16,-8,0)=$D$1,OFFSET(A16,-7,0)="C"),0,C15+1) Cheers, Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200802/1 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Criteria & Return Sequential Count
Hi. Please explain how you would do this on paper. Then we can take your
solution and create a solution in Excel. Tyro "Sam via OfficeKB.com" <u4102@uwe wrote in message news:7f7c995b1f868@uwe... Hi All, I would like a formula that can return a numeric sequential count down a single column based on content of another column. The count will be sequential until it meets all the criteria, wherein a zero (0) should be returned and the count reset, starting at one (1), until the criteria is again met. Match criteria in this order: 1) match specific numeric value (input cell for varying criterion); 106. 2) match specific text value, the letter "C" (static criterion); in row directly below 106. 3) match the same numeric value as in (1) above:106; but this must be exactly 106 rows after matching "C" in (2) above. 4) match specific text value, the letter "C" (static criterion); in row directly below the second 106. Criteria met, I would like the value zero (0) to be returned to the cell that houses the second instance of the text value, letter "C". The data starts in row 4, column "BR" holds numeric and text data. The sequential count should be returned down column "BS". Sample Data: col BS BR row4 101 1 row5 102 2 row6 103 3 row7 104 4 row8 C 5 row9 101 6 row10 102 7 row11 103 8 row12 104 9 row13 105 10 row14 106 11 row15 C 12 row16 101 13 row17 102 14 row18 103 15 row19 104 16 row20 105 17 row21 106 18 row22 107 19 row23 108 20 row24 109 21 row25 110 22 row26 C 23 row27 101 24 row28 102 25 row29 103 26 row30 104 27 row31 105 28 row32 106 29 row33 C 30 row34 101 31 row35 102 32 row36 103 33 row37 104 34 row38 105 35 row39 106 36 row40 C 0 row41 101 1 row42 102 2 Expected Results: Column "BR", Row 40 = 0 Looking for numeric value 106 with "C" on the row below = row 33 and then the same number of rows (106) directly below the first "C", with a "C" on the next row = row 40. The sequential count should return zero (0) in column "BR" row 40. Thanks Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200802/1 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Criteria & Return Sequential Count
Sam,
Wrapping the whole thing in iserror seems to work for your data layout, try this =IF(ISERROR(IF(AND(A4="C",A3=$D$1,OFFSET(A4,$D$1*-1,0)=$D$1,OFFSET(A4,($D$1*-1)+1,0)="C"),0,C3+1)),C3+1,IF(AND(A4="C",A3=$D$1,O FFSET(A4,$D$1*-1,0)=$D$1,OFFSET(A4,($D$1*-1)+1,0)="C"),0,C3+1)) This now includes absolute references to D1 which is the search No and also the amount of rows the dormula looks back to find the previous match. Mike "Sam via OfficeKB.com" wrote: Hi Mike, Many thanks for assistance. Still trying to find a workaround to layout point raised below. Could the MATCH function assist in finding the 1st leg of the variable value and then use OFFSET for the 2nd leg of variable value. Mike H wrote: Sam, I think Iv'e got it Find a variable number x in column A When found there must be a C in the row below Match the variable number x rows above the C The second variable number must also have a C in the row below Yes based upon your data any solution looking for a number higher than 3 will fail because you can't search more than 3 rows back above Row 4 so to get around this instead of looking back x rows as you ask my formula looks back only 8 rows and I start in Row 16 to give some breating space.. My variable number is in D1 Trying to find a workaround. Stuck with the data layout, starting in row 4. When you resolve the above problem you can change the formula change -8 to D1*-1 change -7 to (D1*-1)+1 =IF(AND(A16="C",A15=$D$1,OFFSET(A16,-8,0)=$D$1,OFFSET(A16,-7,0)="C"),0,C15+1) Cheers, Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200802/1 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Criteria & Return Sequential Count
Hi Mike,
Thanks again for further assistance. I've tried the ISERROR formula solution, it does provide a sequential count but does not return the required zero (0). Further assistance appreicated. Cheers, Sam Mike H wrote: Sam, Wrapping the whole thing in iserror seems to work for your data layout, try this =IF(ISERROR(IF(AND(A4="C",A3=$D$1,OFFSET(A4,$D$ 1*-1,0)=$D$1,OFFSET(A4,($D$1*-1)+1,0)="C"),0,C3+1)),C3+1,IF(AND(A4="C",A3=$D$1,O FFSET(A4,$D$1*-1,0)=$D$1,OFFSET(A4,($D$1*-1)+1,0)="C"),0,C3+1)) This now includes absolute references to D1 which is the search No and also the amount of rows the dormula looks back to find the previous match. Mike -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200802/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count if two columns match different criteria | Excel Worksheet Functions | |||
MATCH Multiple Criteria & Return Previous / Penultimate Match | Excel Worksheet Functions | |||
Match 3 Criteria and Return Lowest Numeric Value | Excel Worksheet Functions | |||
Count rows that match 3 sets of criteria? | Excel Worksheet Functions | |||
match multiple criteria & return value from array | Excel Worksheet Functions |