VBA to group a list of names randomly

[Ironman]

Hello - I have an interesting problem. I have a list of 33 names. I want to create a sort of 'speed dating'...ok, not dating, but more like networking. We are cordinating a professional workshop where we went each member to sit at table with 2 other members and exchange ideas for maybe 5 minutes. Then have them pre-assigned to go to another table with another 2 members, and repeat this process until they have met everyone. So I was thinking to set up 11 groups of 3 and then be able to 're-do' the vba code and it would re-group everyone into new groups without repeating names. Each group would meet/network for 5 minutes, then be re-assigned to another group, then again and again until they have met everyone of the 32. Make sense? Or..perhaps the code is run once and all groups are listed out on one worksheet.

I also would like the code to be flexible in that I can re-use it but for a larger list of names, maybe up to 150. Any one have any suggestions on code on this?

[rylo]

Hi

See how this goes. You can expand it to the 150 names, but you would have to muck about with the step value in the outer loop if you want to have larger groups.

Please Login or Register  to view this content.

rylo

[xLJer]

Take six individuals we’ll call A, B, C, D, E, and F. Each group has three per table, so we will start A, B and C at table #1
while D, E and F sit at table #2.

After each group gets acquainted, the moderator announces that everyone is to sit with a new group by joining with two
others who they have not already met.

B and C can no longer sit with A, but B also can not sit with C. So it appears that in the first rotation, person A can only meet
one other person, either D, E, or F, and with only two at the table, this breaks the 3-per-table rule.

And neither B nor C can sit with two others that they have not met yet because any two of the others can not sit together
at the same table.

So we see that at the first rotation, both tables have the same problem; they can’t sit down at a table with
two people who are total strangers to one another.

I conclude that VBA code designed to follow these strict rules to manage unique combinations of groups with
any total number of individuals will, just like I did, run out of combinations before all individuals have been
at a table with others they have not met.

I would like to be corrected if I do not understand the problem. Forum Guru has offered a solution that has
the same people meeting multiple times. Correct me if I’m wrong.
Thanks.

[NickyC]

Hi xlJer

you would be right for a group of 6 people, but with a larger group (33 is the example) i believe it may be possible possible. Not sure how to do it though

rylo's solution goets a lot of the way there but doesn't allow for no 2 people meeting more than once (this may not be completely avoidable)

[Ironman]

Hello NickyC/xLJer/rlyo - - Thanks for jumping in. I did run the code provided by rlyo - I'm not exactly sure what the results are telling me. I've attached a test file that maybe will better show what I'm trying to do. You'll see some comments that I added as well - that better discribes what it is I'm looking to accomplish...thanks again!

[xLJer]

The attached was developed in Excel 2003. It may not be
perfect but "just good enough".

The code looks at all conceivable pairs of row numbers,
as in your example, rows 1-33, and uses a dictionary object
to prevent re-entering any pairs on the worksheet. I've
added headings on row #1 to describe what is listed.

You end up with three names per group, the row # for each name,
and a count of how many times the name is listed.

[jmf39]

Would there be a way to modify this to have groups of 5 or more?

Thanks for your advice.