Below is a proposed solution for the challenge Challenge 16 – Team Pairing
I am going to discuss 2 solutions –
1. One where number of teams is fixed and we are looking at a simple solution.
2. A generic solution which will work for any number of teams. You will need to just do a find and replace on $21 and replace this by required number.
Solution 1 – Simple Solution
1. Create a helper column B and put following formula in B2 and drag down –
=AGGREGATE(14,6,ROW($1:$20)*NOT(COUNTIF(B$1:$B1, ROW($1:$20))), RANDBETWEEN(1,20-ROW($B1)+1))
2. Put following formula in E2 and drag right and down
=INDEX($A:$A,MATCH(ROWS($1:1),$B:$B,0))
2. Put following formula in F2 and drag right and down
=INDEX($A:$A,MATCH(ROWS($1:11),$B:$B,0))
Solution 2 – Generic Solution
1. Create a helper column B and put following formula in B2 and drag down –
=IF(A2="","",AGGREGATE(14,6,(ROW($B$2:$B$21)-ROW($B$2)+1)*NOT(COUNTIF($B$1:B1,(ROW($B$2:$B$21)-ROW($B$2)+1))), RANDBETWEEN(1,ROWS($B$2:$B$21)-ROW($B1)+1)))
This formula generates unique random numbers between 1 and the number of teams. Since, there are 20 teams, hence it will generate 1 to 20 for the number of teams which will be random.
2. Put following formula in E2 and drag right and down
=IF(ROWS(INDIRECT("$1:"&ROWS($1:1)+COLUMNS($A:A)*ROWS($A$2:$A$21)/2-ROWS($A$2:$A$21)/2))<=ROWS($A$2:$A$21)/(3-COLUMNS($A:A)),INDEX($A:$A,MATCH(ROWS(INDIRECT("$1:"&ROWS($1:1)+COLUMNS($A:A)*ROWS($A$2:$A$21)/2-ROWS($A$2:$A$21)/2)),$B:$B,0)),"")
Since this is a case of 20 teams – Hence, it will pick up 1 to 10 and allocate them in column E and in column F, it would put 11 to 20.
Since, in column B numbers were random, hence team allocation will be random in columns E and F.
The solution workbook has been uploaded at following location – Solution – Challenge 16 – Team Pairing