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

Team Generator.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays

COUNTIF(range,criteria)
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

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

RANDBETWEEN(bottom, top)
Returns a random number between the numbers you specify