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?
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().