Allocating people to groups without repetition using Excel

[cruncher08]

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

 

[Drrellik]

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

 

[cruncher08]

Thanks Drrellik for your response.

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

 

[Drrellik]

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.