sortinggoogle-sheets

How to randomly match two names from a list of names in Google Sheets


I have a list of names that are entering a game where they get matched up with other competitors. I'm trying to randomly match the contestants. Once a contestant is matched, they should be removed from the pool and matched with their partner.

Example:

Column A
Lori
Tammy
Jill
Brook
JJ.
Kathi

I want to randomize the matches... So maybe Lori is matched with Brook. Which then would mean Brook needs to be matched to Lori.

I've tried the following: =RANDARRAY(COUNTA(A:A)) In Column B, and then sorting =SORT(A:A, B:B, FALSE) in column C.

This works to randomize... but it doesn't take into account a matching PARTNER.

So an example output of the solution I've tried give me:

Column A Column B Column C **
Lori 0.759334 Brook
Tammy 0.329680 JJ
Jill 0.002556 Lori
Brook 0.986899 Kathi
JJ. 0.974621 Tammy
Kathi 0.413605 Jill

But this obviously would make it difficult to have them be PARTNERS because Lori would be matched with Brook AND Jill.

Can someone help me figure this out please?


Solution

  • Use sort(), randarray() and wraprows(), like this:

    =let(
      names, tocol(A2:A, 1),
      ifna(wraprows(
        sort(names, randarray(rows(names)), true),
        2
      ))
    )
    
    Names Randomized pairs
    Lori Jill Brook
    Tammy Tammy Kathi
    Jill Lori JJ.
    Brook
    JJ.
    Kathi

    See sort(), randarray() and wraprows().