Forum Discussion
Solved
Split uneven teams into new groups
Hi all, Excel 2019, ideally no VBA. I currently have 12 groups with uneven numbers or members (total 100) i.e. G1 - 20 G2 - 19 G3 - 14 G4 - 12 G5 - 8 G6 - 7 G7 - 6 G8 - 5 G9 - 3 ...
AWilkiie Attached a workbook with two possible solutions. One using PowerQuery, the other standard Excel. Both use a randomizer to redistribute persons among 8-9 members groups. If you don't like the distribution, press Refresh All on the Data ribbon and the grouping will change instantly.
See if either of the two could work for you.
A deterministic approach would also be possible if group members were listed 1-100 and then distributed across the new groups, maybe by using the SEQUENCE function.
= LET(
memberNum, SEQUENCE(9,12),
newMemberList, INDEX(MemberList#, memberNum),
IF(memberNum<=100, newMemberList, "")
)