Forum Discussion
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..)
Example combination: Event 1, Day 1, Winner: Team 1.
Zeros are considered did-not-qualify and should not be counted. It is possible to have ties but I'm not sure how to address this. In the sample size below I show 4 teams, however there are upwards of 20 teams and over 40 events over the course of the year. Each event is 3 days and each team's score must be counted separately, I cannot simply add the 3 days together to produce a value.
Any help would be greatly appreciated ! Thanks.
=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.
=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.
I have achieved the desired result using power query.
check the attached worksheet.
Just put your complete data in "A2" cell of INPUT sheet and then refresh the query in RESULT sheet.
let me know if you face any issue.
Adjust the last column M for your real setup.
For day 1:
=LET(d, 1, results, IF(($B$2:$M$2="Day"&d)*($B3:$M3>0), $B3:$M3), m, MIN(results), pos, MATCH(m, results, 0)-d+1, INDEX($B$1:$M$1, pos))
For day 2:
=LET(d, 2, results, IF(($B$2:$M$2="D"&d)*($B3:$M3>0), $B3:$M3), m, MIN(results), pos, MATCH(m, results, 0)-d+1, INDEX($B$1:$M$1, pos))
For day 3:
=LET(d, 3, results, IF(($B$2:$M$2="D"&d)*($B3:$M3>0), $B3:$M3), m, MIN(results), pos, MATCH(m, results, 0)-d+1, INDEX($B$1:$M$1, pos))
- Hello Hans, thank you for the response, I was unable to make it work therefore I went with OliverScheurich's response. The result spilling is mitigable for me.
