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.
If you made the above steps correctly the formula now has a beginning and ending curly bracket, like this:
Don't enter these characters yourself, they appear automatically.
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) or the formula won't work.
Explaining formula in cell C2
Step 1 - Make sure that not more than 5 names have been assigned the same team
The COUNTIF function counts values based on a condition or criteria, the first argument contains an expanding cell reference, it grows when the cell is copied to cells below. This makes the formula aware of values displayed in cells above.