1

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?

0

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.

  • Hi Tyler, thanks a lot that was exactly what I was looking for. I was thinking to complicated. – Moritz Andresen Jan 28 at 16:50
  • looks like this solution generates only "truly unique" pairs eg. it kills all reverse pair duplicates and therefore doesn't solve for OP's "What I want is: ..." coz it returns only 2 possible pairs from input of 4 names, instead of 12 possible pairs from input of 4 names. but who knows... – MARK MY ANSWER Jan 28 at 19:18
  • @MARK, I interpreted the OP to merely want to pair people up randomly one time, not to find all possible pairs. I deduced this because the range in his formula includes 163 names (i.e., finding every pairing between 160+ people seemed impractical and beyond the simple scope of the question, as it would produce over 13,000 possible combinations). – Erik Tyler Jan 28 at 23:07
0

=QUERY(ARRAY_CONSTRAIN(SORT({
 {QUERY(UNIQUE(ARRAYFORMULA(RANDBETWEEN(ROW(INDIRECT("A2:A"&COUNTA(A2:A)*100))^0,
  COUNTA(A2:A)*COUNTA(A2:A)-COUNTA(A2:A)))), "limit "&
  COUNTA(A2:A)*COUNTA(A2:A)-COUNTA(A2:A))}, 
 {ARRAYFORMULA(QUERY(SPLIT(
  TRANSPOSE(SPLIT(REPT(CONCATENATE(A2:A&CHAR(9)), COUNTA(A2:A)),  CHAR(9)))&" "&
  TRANSPOSE(SPLIT(CONCATENATE(REPT(A2:A&CHAR(9),  COUNTA(A2:A))), CHAR(9)))," "),
  "select Col1 where Col1<>Col2", 0)&" - "&QUERY(SPLIT(
  TRANSPOSE(SPLIT(REPT(CONCATENATE(A2:A&CHAR(9)), COUNTA(A2:A)),  CHAR(9)))&" "&
  TRANSPOSE(SPLIT(CONCATENATE(REPT(A2:A&CHAR(9),  COUNTA(A2:A))), CHAR(9)))," "),
  "select Col2 where Col1<>Col2", 0))}}, 1, 1), 4, 2), "select Col2", 0)

  • note1: all you need is column A populated with names
  • note2: if you want more/fewer pairs then change 4 to the desired number

if you want it to be in "each pair name in own column" mode, then:

=ARRAYFORMULA(SPLIT(QUERY(ARRAY_CONSTRAIN(SORT({
 {QUERY(UNIQUE(ARRAYFORMULA(RANDBETWEEN(ROW(INDIRECT("A2:A"&COUNTA(A2:A)*100))^0,
  COUNTA(A2:A)*COUNTA(A2:A)-COUNTA(A2:A)))), "limit "&
  COUNTA(A2:A)*COUNTA(A2:A)-COUNTA(A2:A))}, 
 {ARRAYFORMULA(QUERY(SPLIT(
  TRANSPOSE(SPLIT(REPT(CONCATENATE(A2:A&CHAR(9)), COUNTA(A2:A)),  CHAR(9)))&" "&
  TRANSPOSE(SPLIT(CONCATENATE(REPT(A2:A&CHAR(9),  COUNTA(A2:A))), CHAR(9)))," "),
  "select Col1 where Col1<>Col2", 0)&" - "&QUERY(SPLIT(
  TRANSPOSE(SPLIT(REPT(CONCATENATE(A2:A&CHAR(9)), COUNTA(A2:A)),  CHAR(9)))&" "&
  TRANSPOSE(SPLIT(CONCATENATE(REPT(A2:A&CHAR(9),  COUNTA(A2:A))), CHAR(9)))," "),
  "select Col2 where Col1<>Col2", 0))}}, 1, 1), 4, 2), "select Col2", 0), " - "))

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.