Google Sheets – Create Random Pairs Without Duplicates

concatenateformulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-query

I try to randomly pair people into unique pairs.

I have two columns one with helping values created via Rand() and one that ranks upon these values with 

=INDEX($A$2:$A$164,RANK(S2,$S$2:$S$164))

However, this results in a lot of duplicates and circles in the two pairs:

  1. Phillip – Hendrik
  2. Fran – Lucy
  3. Lucy – Phillip
  4. Hendrik – Hendrik

What I want is:

  1. Phillip – Hendrik
  2. Fran – Lucy
  3. Lucy – Fran
  4. Hendrik – Phillip

I could easily half the two groups but this does not seem to be the best solution and also not really random if I do it manually.

Any ideas?

Best Answer

Moritz. Immediately, I notice that your range contains an odd number of items ($A$2:$A$164 contains 163 cells); so not everyone will be paired with someone. In any solution, then, in order to include all names, you'd need to make sure your range always has an even number of cells—in this case one more than there are number of people (i.e., $A$2:$A$165).

Also, you'll want to be certain that your File > Spreadsheet settings > Calculation setting is set to "on change" or your list will be regenerated automatically every minute or every hour, depending on which other setting is currently selected.

That said, I'm going to start from scratch, ignoring any columns you may have set up other than the names in Column A. Then you can apply this to your actual setup:

  1. Clear Column B entirely in preparation for an array formula. This will be a "helper column" which can hide later if you like.

  2. Place the following formula into B2:

=ArrayFormula(QUERY({A2:A165,RANDBETWEEN(0 * ROW(A2:A165), 100000000000000)/100000000000000},"Select Col1 Order By Col2"))

  1. Place the following formula anywhere else that has two open columns side by side (say, C2, for the sake of argument):

=ArrayFormula({QUERY({ROW(B2:B165)-1,B2:B165},"Select Col2 Where Col1 <= "&ROWS(B2:B165)/2),QUERY({ROW(B2:B165)-1,B2:B165},"Select Col2 Where Col1 > "&ROWS(B2:B165)/2)})

The Column B formula will provide a randomized, non-duplicating list of your names.

The Column C formula will take the top half of that list and pair it with the bottom half.

You can't (to my knowledge) merge this into one formula, because randomizing functions are triggered by each sub-formula within a longer formula, so you'd wind up with duplicates. By creating a single helper column with RAND(), we can refer to it with the other formula while not getting duplicates.

Related Topic