Forum Discussion

Copper Contributor

Mar 18, 2024

Solved

Formula to Identify Winning Team by Team Name Over Different Days and Different Events

Hi All, Need help with this one. I'm trying to find the winning team that wins specific events on specific days based on LOWEST score. They should be identified by name (Team 1, Team 2, etc.. etc...

excel

Formulas and Functions

OliverScheurich
Mar 18, 2024
FrenchyFri27
=LET(rng,B3:M7,
DROP(REDUCE("",SEQUENCE(1,3),
LAMBDA(a,b,HSTACK(a,REDUCE("",SEQUENCE(ROWS(rng)),
LAMBDA(u,v,
LET(z,CHOOSECOLS(WRAPROWS(CHOOSEROWS(rng,v),3),b),
VSTACK(u,
TEXTJOIN(", ",,FILTER(CHOOSECOLS(WRAPROWS(B1:M1,3),b),IF(z>0,z)=MIN(IF(z>0,z)))))))))))
,1,1))

This formula which spills the result could be an alternative. In my example i don't work with merged cells in row 1 in order to avoid potential problems.

OliverScheurich

Gold Contributor

Mar 18, 2024

FrenchyFri27

=LET(rng,B3:M7,

DROP(REDUCE("",SEQUENCE(1,3),

LAMBDA(a,b,HSTACK(a,REDUCE("",SEQUENCE(ROWS(rng)),

LAMBDA(u,v,
LET(z,CHOOSECOLS(WRAPROWS(CHOOSEROWS(rng,v),3),b),

VSTACK(u,

TEXTJOIN(", ",,FILTER(CHOOSECOLS(WRAPROWS(B1:M1,3),b),IF(z>0,z)=MIN(IF(z>0,z)))))))))))

,1,1))

This formula which spills the result could be an alternative. In my example i don't work with merged cells in row 1 in order to avoid potential problems.