This blog article describes how to create teams randomly.
There are twenty names in column B and four teams in column E.
There will be five team members in each team.
In column C a team is randomly selected in each cell.
Array formula in cell C2:
=INDEX($E$2:$E$5, LARGE((COUNTIF($C$1:C1, $E$2:$E$5)<5)*(ROW($1:$4)), RANDBETWEEN(1, SUMPRODUCT(--(COUNTIF($C$1:C1, $E$2:$E$5)<5))))) + CTRL + SHIFT + ENTER. Copy cell C2 and paste it down.
COUNTIF($C$1:C1, $E$2:$E$5)<5 makes sure that each team has max five team members.
ROW($1:$4) gives a row number to each team. Example, if you have three teams, change it to ROW($1:$3) otherwise the formula won´t work.
Download excel sample file for this tutorial
(Excel 97-2003 Workbook *.xls)
Functions in this article:
SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays
Counts the number of cells within a range that meet the given condition
LARGE(array,k) returns the k-th largest row number in this data set.
ROW(reference) returns the rownumber of a reference
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
Returns a random number between the numbers you specify