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.
 

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