Trying to get a formula to show last 5 most recent text values in a column corresponding to a name

[SW82SW]

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

=
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.

 

[Cubist]

Try:

Book1
	A	B	C	D	E	F	G
1	Game week	Tipper	Selection	Result		Tipper	Form
2	1	Brian	A	Y		Brian	W, L, W, W, W
3	1	Matthew	B	N		Jon	W, W, W, L, W
4	1	Jon	C	Y		Matthew	L, L, L, V, W
5	2	Brian	D	N
6	2	Matthew	B	N
7	2	Jon	S	Y
8	3	Brian	T	Y
9	3	Matthew	A	N
10	3	Jon	B	Y
11	4	Brian	R	Y
12	4	Matthew	T	V
13	4	Jon	C	N
14	5	Brian	C	Y
15	5	Matthew	D	Y
16	5	Jon	A	Y
17
Sheet4

Cell Formulas
Range	Formula
F2:G4	F2	=GROUPBY(B2:B16,IF(D2:D16="Y","W",IF(D2:D16="N","L","V")),LAMBDA(x,LET(t,TEXTJOIN(", ",1,x),TEXTAFTER(t,", ",-5,,,t))),,0)
Dynamic array formulas.

 

[SW82SW]

Hi Cubist, thanks for this but I don't seem to have the GROUPBY function.

 

[Cubist]

I don't seem to have the GROUPBY function.

Try:

Book1
	A	B	C	D	E	F	G
1	Game week	Tipper	Selection	Result		Tipper	Form
2	1	Brian	A	Y		Brian	L, W, W, W, L
3	1	Matthew	B	N		Matthew	L, L, L, V, W
4	1	Jon	C	Y		Jon	W, W, W, L
5	2	Brian	D	N
6	2	Matthew	B	N
7	2	Jon	S	Y
8	3	Brian	T	Y
9	3	Matthew	A	N
10	3	Jon	B	Y
11	4	Brian	R	Y
12	4	Matthew	T	V
13	4	Jon	C	N
14	5	Brian	C	Y
15	5	Matthew	D	Y
16	5	Brian	A	N
Sheet4

Cell Formulas
Range	Formula
F2:G4	F2	=LET(b,B2:B16,d,D2:D16,u,UNIQUE(b),HSTACK(u,DROP(REDUCE("",u,LAMBDA(x,y,VSTACK(x,TEXTJOIN(", ",,TAKE(FILTER(IF(d="Y","W",IF(d="N","L","V")),b=y),-5))))),1)))
Dynamic array formulas.

 

[Cubist]

Overcomplicated the one above a bit, can also try this:

=LET(b,B2:B16,d,D2:D16,u,UNIQUE(b),HSTACK(u,BYROW(u,LAMBDA(x,TEXTJOIN(", ",,TAKE(FILTER(IF(d="Y","W",IF(d="N","L","V")),b=x),-5))))))

 

[SW82SW]

Sorry to be a pain, I tried to replicate this and change it to what I needed but can't get it to work. I am trying to get it to work picking up the names and "Y", "N", or "V" from one tab and displaying the results on another tab in a table.

This is a screenshot of my data. I need this to be dynamic as every week I will be adding another line in for each tipper so I need it to read all of the rows in Bets!P:P but ignore any values apart from "Y", "N" or "V". The default value is "P" for pending until the bet has resulted and this "P" is in every cell in from Bets!P11:P10000.

I am trying to get it to output into the highlighted cells in the table on Dashboard Data

I put the simplified version at the top of the question as I thought I would be able to adapt it to what I need but I can't seem to do it.

Thanks so much for your help.

 

[Cubist]

read all of the rows in Bets!P:P but ignore any values apart from "Y", "N" or "V". The default value is "P" for pending until the bet has resulted and this "P" is in every cell in from Bets!P11:P10000.

I cannot tell which column is P since you didn't show the address headers.

Are the only values in column P are "Y","N","V" and "P"? Nothing else?

You didn't mention you're using a table object. Spill formula won't work.

 

[SW82SW]

Sorry, column P is the "Win" column (last one shown in top image). Yes, they are the only values. I didn't realise the table would make a difference, sorry.

 

[Cubist]

Book1
	M	N	O	P	Q	R	S
1
2
3
4
5
6
7
8
9
10	Game week	Tipper	Selection	Result		Tipper	Form
11	1	Brian	A	Y		Brian	W, L, W, W, W
12	1	Matthew	B	N		Matthew	L, L, L, V
13	1	Jon	C	Y		Jon	W, W, W, L, L
14	2	Brian	D	N
15	2	Matthew	B	N
16	2	Jon	S	Y
17	3	Brian	T	Y
18	3	Matthew	A	N
19	3	Jon	B	Y
20	4	Brian	R	Y
21	4	Matthew	T	V
22	4	Jon	C	N
23	5	Brian	C	Y
24	5	Matthew	D	P
25	5	Jon	A	N
Bets

Cell Formulas
Range	Formula
S11:S13	S11	=LET( f,FILTER(HSTACK(Bets!N11:N1000,Bets!P11:P1000),Bets!P11:P1000<>"P"), TEXTJOIN(", ",,TAKE(XLOOKUP(FILTER(INDEX(f,,2),INDEX(f,,1)=[@Tipper]),{"Y","N","V"},{"W","L","V"}),-5)) )

 

[SW82SW]

Thank you so much, this worked perfectly.