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

SW82SW

New Member
Joined
Feb 10, 2025
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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

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 weekTipperSelectionResult
1BrianAY
1MatthewBN
1JonCY
2BrianDN
2MatthewBN
2JonSY
3BrianTY
3MatthewAN
3JonBY
4BrianRY
4MatthewTV
4JonCN
5BrianCY
5MatthewDY
5JonAY

I would ideally like to output either of the below (one cell answer preferred)


TipperForm
BrianW, W, W, L, W
MatthewW, V, L, L, L
JonW, L, W, W, W

TipperForm
BrianWWWLW
MatthewWVLLL
JonWLWWW

Thanks guys.
 
Try:
Book1
ABCDEFG
1Game weekTipperSelectionResultTipperForm
21BrianAYBrianW, L, W, W, W
31MatthewBNJonW, W, W, L, W
41JonCYMatthewL, L, L, V, W
52BrianDN
62MatthewBN
72JonSY
83BrianTY
93MatthewAN
103JonBY
114BrianRY
124MatthewTV
134JonCN
145BrianCY
155MatthewDY
165JonAY
17
Sheet4
Cell Formulas
RangeFormula
F2:G4F2=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.
 
Upvote 0
I don't seem to have the GROUPBY function.
Try:
Book1
ABCDEFG
1Game weekTipperSelectionResultTipperForm
21BrianAYBrianL, W, W, W, L
31MatthewBNMatthewL, L, L, V, W
41JonCYJonW, W, W, L
52BrianDN
62MatthewBN
72JonSY
83BrianTY
93MatthewAN
103JonBY
114BrianRY
124MatthewTV
134JonCN
145BrianCY
155MatthewDY
165BrianAN
Sheet4
Cell Formulas
RangeFormula
F2:G4F2=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.
 
Upvote 0
Overcomplicated the one above a bit, can also try this:
Excel Formula:
=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))))))
 
Upvote 0
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.

1739754327570.png


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

1739754661528.png


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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Book1
MNOPQRS
1
2
3
4
5
6
7
8
9
10Game weekTipperSelectionResultTipperForm
111BrianAYBrianW, L, W, W, W
121MatthewBNMatthewL, L, L, V
131JonCYJonW, W, W, L, L
142BrianDN
152MatthewBN
162JonSY
173BrianTY
183MatthewAN
193JonBY
204BrianRY
214MatthewTV
224JonCN
235BrianCY
245MatthewDP
255JonAN
Bets
Cell Formulas
RangeFormula
S11:S13S11=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)) )
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,918
Members
453,766
Latest member
Gskier

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top