Maximise the number of new encounters between guests at a meeting

Question

I am hosting a series of meetings. There are 24 guests. Two or three times each meeting, we break out into different subgroups of between 2-6 people. I would like to maximise the new encounters so I am looking for an algorithm to help me make new matches, so everyone can meet everyone else.

My current idea is to record the data in Google Sheets and then use the QUERY function to analyse the data. (QUERY is very similar to SQL syntax.)

Here's the 'Round1' table:

subgroup1 |  subgroup2
==========|==========
Adam      |  Edith
Ben       |  Fran
Chris     |  Gary
Dave      |

And the table for 'Round2' looks like this:

subgroup1 |  subgroup2 | subgroup3 
==========|============|===========
Adam      |  Ben       | Dave
Gary      |  Fran      | Edith
Chris     |            |

What I want to do is consume that data and output a chart like this which shows me who has met whom:

      Adam     Ben     Chris    Dave   Edith   Fran    Gary
Adam   X       TRUE    TRUE     TRUE   FALSE   FALSE   TRUE
Ben   TRUE      X      TRUE     TRUE   FALSE   TRUE    FALSE
Chris TRUE     TRUE     X       TRUE   FALSE   FALSE   TRUE
Dave  etc...
Edith etc...
Fran  etc..
Gary  etc...

Could anyone help me to think through how I can use QUERY/SQL to turn those input tables into that output chart?

user_11075129 · Answer

If you look in cell B3 on the new tab on your shared sheet called MK.Help, you'll find this single formula:

=ARRAYFORMULA(IF((B2:2="")+(ROW(A3:A)>COLUMN(B2:2)),,COUNTIF(QUERY(TRANSPOSE('One table'!A:E),,9),"*"&A3:A&"*"&B2:2&"*")+COUNTIF(QUERY(TRANSPOSE('One table'!A:E),,9),"*"&B2:2&"*"&A3:A&"*")))

That is telling you the counts for a heat map based on the tab called OneTable and nothing else. It will self populate indefinitely as you add groups to the OneTable tab.

Is that what you're going for?

user_6293159 · Answer

I have come up with an answer, with a very laborious process! Maybe if I explain it here, someone can help me think how to make this simpler.

I start with the example data in this sheet.

In the original question I described one table per round, but I don't care in which round someone met, so I can combine all that data into one table. So in this table, each row shows one of the subgroups:

Then I do a long sequence of match(), transpose(), filter(), join(), split(), until I get this table which shows who has met whom:

From there, it is not so hard to generate the output chart I was looking for

This works! But it is not very elegant. I will love to make this simpler.

Maximise the number of new encounters between guests at a meeting

2 Answers