Forum Discussion
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
G10 - 3
G11 - 2
G12 - 1
I want to split as evenly as possible all members across 12 new groups. I have seen this done in a variety of ways, Yet the way I've seen it (or got it working), some new groups have multiple members from old groups and some new groups can have none.
The new groups have 8 or 9 members depending on the split.
The step I want to add is to have as few members together from the old groups in the new groups as possible. i.e. There shouldn't be a new group that contains old members unless the old group had 12 members or more.
I've drawn it out manually and I think the 9 member groups will only repeat an old group twice.
Thanks
A
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.
3 Replies
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, "") )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.
- Thanks for this. PQ isn't something I use much. I hadn't thought of the MOD function.
