Excel BI Challenge Workout 041

Workouts Excel BI Challenge
1

— CAN YOU SOLVE THIS - EXCEL CHALLENGE 41 —
(Power Query solutions are also welcome for Excel Challenges)

Do a random allocation of members given in A2:A18 in 4 different teams (3 persons per team).

It should be random and no-one should be repeated anywhere else. If more than 12, remaining members should come under Reserve.

Reserve also need to be random.

Since this is random, hence everybody will get different answer and also every refresh will give a different result.

(Post answers in Comment. Your formula need not be different from others as long as you have worked out your formula independently)

Download Practice File - https://lnkd.in/d-aRg7e9

#excel, #challenge, #advancedexcel, #excelchallenge, #excelproblem, #excelquestion, #excelsolution, #excelformulas, #excelfunctions, #exceltips, #exceltricks, #powerquerychallenge, #powerbichallenge, #powerqueryproblem, #M, #powerpivot

image
image1042×610 82.5 KB

Excel BI’s LinkedIn Post:

2 
=LET(_m,A2:A18,
_rand,SORTBY(_m,RANDARRAY(SEQUENCE(ROWS(_m)),4,1,ROWS(_m))),
_teams,WRAPCOLS(_rand,3),
_fourTeams,TAKE(_teams,,4),
_reserve,TOCOL(TAKE(_teams,,-2),2),
_result,IFNA(HSTACK(_fourTeams,_reserve),""),
_h,HSTACK("Team "&SEQUENCE(,4),"Reserve"),
VSTACK(_h,_result))

Random Team Allocation - Quadri Atharu.xlsx (25.3 KB)