I now know how to choose a random value from one column, by using =INDEX with RANDBETWEEN, MATCH, FILTER, etc. Now I have the following sheet:
| Task | Available person 1 | Available person 2 | Available person 3 | Available person 4 | Available person 5 | Chosen person 1 | Chosen person 2 | ||
|---|---|---|---|---|---|---|---|---|---|
| Mow the grass | Tyler | Ross | Paul | Tammy | Lisa | x | x | ||
| Cook dinner | Samantha | William | Peter | Mia | Tim | x | x |
Is there a formula to choose two random persons from available 1 - available 5 to do the task? I want the first random chosen person to appear in the column "Chosen person 1" and the second in "Chosen person 2". They cannot be the same person, so if it's possible I would like to filter out duplicates.
CodePudding user response：
try:
=ARRAYFORMULA(ARRAY_CONSTRAIN(SPLIT(FLATTEN(QUERY(QUERY(QUERY(SPLIT(FLATTEN(
ROW(A2:A3)&"×"&RANDARRAY(ROWS(B2:F3), COLUMNS(B2:F3))&"×"&B2:F3), "×"),
"select max(Col3) group by Col2 pivot Col1"),
"offset 1", 0),,9^9)), " "), 9^9, 2))
