Hi,
I am trying to find a formula that would look through all results in a column that correspond to a name in another column and have it show me the last 5 results for each person. I need results that show "Y" to be shown as "W", "N" to show as "L" and "V" to remain as "V" (W, L, or V). This list will grow over time so I need a formula that continues to only select the 5 most recent results.
I can't use XL2BB so I have attached a small table with an example of my data and a copy of what I would like. I have tried the following formula but it only shows the first letter for in each cell ("W") rather than what the last 5 results have been
I would ideally like to output either of the below (one cell answer preferred)
Thanks guys.
I am trying to find a formula that would look through all results in a column that correspond to a name in another column and have it show me the last 5 results for each person. I need results that show "Y" to be shown as "W", "N" to show as "L" and "V" to remain as "V" (W, L, or V). This list will grow over time so I need a formula that continues to only select the 5 most recent results.
I can't use XL2BB so I have attached a small table with an example of my data and a copy of what I would like. I have tried the following formula but it only shows the first letter for in each cell ("W") rather than what the last 5 results have been
PHP:
=
CHOOSE(1+
IFERROR(
MOD(
LARGE(
+($B$1:$B1=B2)*(100*ROW($D:$D)+3*($D1:$D$1="Y")+2*($D1:$D$1="N")+1*($D1:$D$1="V")),
{5,4,3,2,1}
),
100
),
0
),
"W","L","V"
)
Game week | Tipper | Selection | Result |
1 | Brian | A | Y |
1 | Matthew | B | N |
1 | Jon | C | Y |
2 | Brian | D | N |
2 | Matthew | B | N |
2 | Jon | S | Y |
3 | Brian | T | Y |
3 | Matthew | A | N |
3 | Jon | B | Y |
4 | Brian | R | Y |
4 | Matthew | T | V |
4 | Jon | C | N |
5 | Brian | C | Y |
5 | Matthew | D | Y |
5 | Jon | A | Y |
I would ideally like to output either of the below (one cell answer preferred)
Tipper | Form |
Brian | W, W, W, L, W |
Matthew | W, V, L, L, L |
Jon | W, L, W, W, W |
Tipper | Form | ||||
Brian | W | W | W | L | W |
Matthew | W | V | L | L | L |
Jon | W | L | W | W | W |
Thanks guys.