# Allocating people to groups without repetition using Excel

#### cruncher08

##### New Member
Hi guys,

Not sure whether Excel can be used to solve this issue. Would appreciate any advise.

Basically, I have a group of 20 people. I would to break them up into groups of 4 (i.e. there will be 5 groups of 4 people each). After the first round, all the 20 people will be reassigned to new groups such that the members are new to each other (i.e. they have not met each other before during earlier rounds).

It will be a recursive process until everyone has met every other person (i.e. need Excel to figure out (i) the minimum number of rounds required and (ii) assignment of individuals to different groups each round to ensure they meet/work with new people each time).

Hoping it can be done with Excel and/or macros

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
well not sure if I cant help in excel but just looking at the numbers you have 20 in a group. and 5 groups of 4. if you look at it this way 1+3 +3 +3 +3 +3 +3 +3(1st duplicate) person 1 plus 3 in first group is 4 and it looks like it will take 7 reassignments for the 1st person to sit with all 19 others in the group and 1 duplicate from the first group as a minimum. maybe even a few more time to get everyone to sit with everyone.

hope this helps, as a bump too...

~DR

I did some further searches on this problem and have come to realize that this is known mathematically as the Social Golfer problem. Seems to require some sort of "combinatorial optimization" that cannot be easily solved in Excel. Quite a few studies have been done on similar problems with no perfect solutions found so far

well not sure if I cant help in excel but just looking at the numbers you have 20 in a group. and 5 groups of 4. if you look at it this way 1+3 +3 +3 +3 +3 +3 +3(1st duplicate) person 1 plus 3 in first group is 4 and it looks like it will take 7 reassignments for the 1st person to sit with all 19 others in the group and 1 duplicate from the first group as a minimum. maybe even a few more time to get everyone to sit with everyone.

hope this helps, as a bump too...

~DR

perhaps modifying your group size as you move would help make it seems less daunting. also you could give each member a card with all 20 names printed on it, when they meet in a group mark out the persons name and control the first few regroups then by the 4th let the group look for those that they still have on their card. might be a fun experiment.

Replies
0
Views
204
Replies
0
Views
73
Replies
0
Views
825
Replies
0
Views
154
Replies
0
Views
542

1,203,248
Messages
6,054,376
Members
444,721
Latest member
BAFRA77

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back