Smart way to form "Speed-Dating" Groups from 100+ People

[Mushral]

Hello all,

I have a list of 102 people in my organization who need to "get to know eachother" by going on "speed dates" with eachother. 102 people getting to know eachother during 1 on 1's would mean a lot of meetings which would cost a lot of time, so the idea of my organization is to organize lunch settings in groups of 6. So every week there will be a lunch session where all 102 people are matched in groups of 7 ( = 17 groups) so that during 1 session you will automatically get to know 5 new people. The goal is that by the end of the year, everyone has had one speed-date session with everyone on the list. That means that per lunch session, each person will meet with 5 other persons. The next week, another lunch session will be held, and everyone has to be matched up with 5 new/different persons in order to make the session as efficient as possible.

That means that each lunch session, there will be 17 groups of 6 people ( = 102). The next week, the same group of 102 has to be split up in 17 new groups, but everybody has to be matched up with only new faces, so nobody from Round 1 can be in the same group again. The week after that, the same principle applies again, and nobody can be matched up with anyone they have been matched up already during week 1 or week 2.

Basically, I'm looking for a way that Excel generates 17 groups of 6 people from the list of 102 for a number of "rounds" until everyone has been grouped with everyone once. At the same time I want to prevent that people get matched up with the same people in different rounds that they have already had a lunch session with.

My list is a flat list of 102 names that are listed in column A so in Cell A1:A102\

Does anyone know a quick/smart/easy way to fix this in Excel? Thanks in advance!

 

[sandy666]

something like this?

 

[Mushral]

The point is that that list is just "1 round" of meetings. So in your example, Person 001 in group 0 will only meet person 002, 003, 004, 005, 006. The point is that every person gets to meet with everyone in the organization. That's why you need multiple rounds. During round 2, Person 001 has to be matched up with a new group, but 002,003,004,005,006, can no longer be a part of that group, because 001 has already met with those people, so for example 001 will during round 2 be matched up with 007,008,009,010,011,. After round 2 he will have then met with 10 out of 102 people. Round 3, he will again meet up with new people, so for example with 012,013,014,015,016. So after round 3, he has met up with 15 out of 102 people.

If I would just use a normal "Random Generator" each round, then there is no way to track who has already met who, and theoretically it would be possible to have 100 rounds of meetings and still only meeting up with 50% of the entire group of 102 people. So I want to make a sort of "Random Group" generator that eliminates people from the list you already met up with, during a previous round.

So I believe it's a bit more complicated than what you proposed.

 

[sandy666]

bad idea - deleted
but if one person to Groups which doesn't contain this person = 1633 meetings

 

[sandy666]

correction: 1632

and so on...

 

[Mushral]

I believe you don't follow me completely, maybe I'm not fully clear. In the example you just posted the same 5 people are meeting 1 new person every round. That way we will indeed need a lot of meetings until everyone has met everyone. The idea is that the full group gets randomly assigned with 6 complete new people who all don't know eachother yet, every round, until everyone has met eachother.

I can only explain it best by moving away from the Excel for a second and try to visualize the concept. Imagine a big restaurant where there are 17 tables. At each table 6 people can take a seat. In total we can seat 102 people, which is equal to the number of people in Organization X. If everyone takes a random seat at a random table, everyone will be seated at the same table with 5 other people that they don't know. Let's give each table number a number, so we have table #1 to table #17. When the night ends, everyone who was seated at table #1 will know eachother. Let's say the same organization goes out to the same restaurant the week after, but everyone needs to be seated at a table that doesn't include anyone they have spoken before during the previous dinner. So everyone who was seated at table #1 has to be scattered across different tables. But at the same time, everyone who was at table #2 has to be seated at different tables also, and can not be matched up with other people who were at table #2 before. So during the 2nd dinner, we need a new "Random Number Generator", but every person should no longer be randomly assigned to any 5 people of the full group of 102, but the options are limited to the 97 (102 - 5) people that he/she hasn't been matched with yet. So it's like a Random Generator that excludes the 5 people from the previous round.

This process will go on, until everyone has met everyone. So during round 3, the "options" for the random group generator should not be 102, but 92 (102 - 5 - 5).

So every round has 17 meetings (17 groups of 6 people), and I believe after about 11 rounds everyone should be able to have met eachother if they are assigned to a group of completely new people each round. So that would be about 190~ meetings at max in total.

 

[sandy666]

I'd like to see example of expected result.

 

[Mushral]

Let's say the group is smaller so I can provide you an example of expected result. Let's say it the group is 9 people (#1 - #9) who can meet in groups of 3.



With meetings of 3 people, every person can meet up with 2 new people and thus get to know 2 new people each meeting. So in order for 1 person to know all 8 others, you need atleast 8 meetings. If I would use a random number generator then Excel could generate random teams but in theory it would be possible to meet 10 times without ever meeting person #8 for example. So I need a smart way to calculate that everyone meets with everyone, without double-meeting with people they already saw before.

I would expect a result in shape of a table in Excel that shows a schedule that makes sure everyone of the 9 people will meet up with everyone in an efficient way as possible, such as below:

Week 1:
Meeting A: #1, #2, #3
Meeting B: #4, #5, #6
Meeting C: #7, #8, #9

Week 2:
Meeting A: #1, #4, #5
Meeting B: #2 #6 #7
Meeting C: #3 #8 #9

Week 3
Meeting A: #1, #6 #7
Meeting B: #2 #8 #9
Meeting C: #3 #4 #5

Week 4
Meeting A: #1, #8, #9
Meeting B: #2, #4, #5
Meeting C: #3 #6 #7


By this table you see that in 4 weeks everyone has met up with all 8 others from the group of 9. So I'm basically looking for a schedule like this.

In this case I did it from head because it's do-able for groups of 3 with only 9 people, but now in my example I have a list of 102 people who can meet in groups of 6, so that makes it more complicated:

 

[sandy666]

I can generate new 17 groups with 6 people (no repetition) every time I sort the source table (RND), but I have no idea how to do it your way

 

[Mushral]

I see, but in the example you showed you see for example that in screenshot 1 person P001 is matched up with P002-P006. Then in screenshot 3 you see that P001 is matched up with P006 again. So by that definition this system will not make sure that P001 will meet up with everyone of the 102 people, because there is no way to track "who has already been matched" (= should not be included in 'new round' group generator) and "who has not been matched yet" (= should be included in 'new round' group generator)

I'm not sure if it's even possible to do it in Excel, maybe it's more of a statistical/logical thing that has to be written out. Just looking for a smart way because the number of possible combinations and people is too big to just "start writing".

In any case thanks for the help/support, even if you don't know a final solution I still appreciate it.