Hello. I have a question building on some of the help I've received here a month or so ago.
I have some data that looks something like:
Row 1: P-150, P-150, P-150, P-150, P-150, P-150, P-150, P-150, P-150, P-100, P-100, P-100
Row 2: , P, D, D, 1, 1, 2, 2, 2, , P, 1
Row 3: U, U, U, U, D, 125, U, D, 150, U, U, 50
Row 4: 100, 175, 166, 160, 154, 130, 160, 158, 151, 25, 75, 54
Row 5: 105, 166, 160, 154, 130, 125, 158, 151, 150, 26, 70, 50
Cell A8 = P-150
Cell A9 = 1
Formula in Cell A13 through A30 =
=IF((ROW() - 12)>SUMPRODUCT(SIGN(($A$2:$L$2=$A$9)+($A$2:$L$2="")+($A$2:$L$2="P")+($A$2:$L$2="D")),--($A$1:$L$1=$A$8))+1,"",INDEX($4:$4,SMALL(IF(($A$1:$L$1=$A$8)*SIGN(($A$2:$L$2=$A$9)+($A$2:$L$2="")+($A$2:$L$2="P")+($A$2:$L$2="D")),COLUMN($A$2:$L$2),""),ROW()-12)))
Results look like:
100
175
166
160
154
130
(now a bunch of empty cells)
This works exactly like I had hoped; however, now I would like to complicate the formula a little more. I would like one more number to be pulled after the 130. I would like that number to be the number in row 5 corresponding to the last value pulled (i.e. 130 was last number, and 125 is in the same column one row down, so pull 125.
The results would read:
100
175
166
160
154
130
125
Can anyone help with this new task?
I started adjusting the formula to this point:
=IF((ROW() - 12)>SUMPRODUCT(SIGN(($A$2:$L$2=$A$9)+($A$2:$L$2="")+($A$2:$L$2="P")+($A$2:$L$2="D")),--($A$1:$L$1=$A$8))+1,"",IF((ROW() - 12)<SUMPRODUCT(SIGN(($A$2:$L$2=$A$9)+($A$2:$L$2="")+($A$2:$L$2="P")+($A$2:$L$2="D")),--($A$1:$L$1=$A$8))+1,INDEX($4:$4,SMALL(IF(($A$1:$L$1=$A$8)*SIGN(($A$2:$L$2=$A$9)+($A$2:$L$2="")+($A$2:$L$2="P")+($A$2:$L$2="D")),COLUMN($A$2:$L$2),""),ROW()-12)),"new number here"))
I've replaced "new number here" with a few tries but have not had any success pulling the formula.
I can send the excel file if needed.
Thanks for any help.
Mark
I have some data that looks something like:
Row 1: P-150, P-150, P-150, P-150, P-150, P-150, P-150, P-150, P-150, P-100, P-100, P-100
Row 2: , P, D, D, 1, 1, 2, 2, 2, , P, 1
Row 3: U, U, U, U, D, 125, U, D, 150, U, U, 50
Row 4: 100, 175, 166, 160, 154, 130, 160, 158, 151, 25, 75, 54
Row 5: 105, 166, 160, 154, 130, 125, 158, 151, 150, 26, 70, 50
Cell A8 = P-150
Cell A9 = 1
Formula in Cell A13 through A30 =
=IF((ROW() - 12)>SUMPRODUCT(SIGN(($A$2:$L$2=$A$9)+($A$2:$L$2="")+($A$2:$L$2="P")+($A$2:$L$2="D")),--($A$1:$L$1=$A$8))+1,"",INDEX($4:$4,SMALL(IF(($A$1:$L$1=$A$8)*SIGN(($A$2:$L$2=$A$9)+($A$2:$L$2="")+($A$2:$L$2="P")+($A$2:$L$2="D")),COLUMN($A$2:$L$2),""),ROW()-12)))
Results look like:
100
175
166
160
154
130
(now a bunch of empty cells)
This works exactly like I had hoped; however, now I would like to complicate the formula a little more. I would like one more number to be pulled after the 130. I would like that number to be the number in row 5 corresponding to the last value pulled (i.e. 130 was last number, and 125 is in the same column one row down, so pull 125.
The results would read:
100
175
166
160
154
130
125
Can anyone help with this new task?
I started adjusting the formula to this point:
=IF((ROW() - 12)>SUMPRODUCT(SIGN(($A$2:$L$2=$A$9)+($A$2:$L$2="")+($A$2:$L$2="P")+($A$2:$L$2="D")),--($A$1:$L$1=$A$8))+1,"",IF((ROW() - 12)<SUMPRODUCT(SIGN(($A$2:$L$2=$A$9)+($A$2:$L$2="")+($A$2:$L$2="P")+($A$2:$L$2="D")),--($A$1:$L$1=$A$8))+1,INDEX($4:$4,SMALL(IF(($A$1:$L$1=$A$8)*SIGN(($A$2:$L$2=$A$9)+($A$2:$L$2="")+($A$2:$L$2="P")+($A$2:$L$2="D")),COLUMN($A$2:$L$2),""),ROW()-12)),"new number here"))
I've replaced "new number here" with a few tries but have not had any success pulling the formula.
I can send the excel file if needed.
Thanks for any help.
Mark