## Team Generator in excel

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

### 4 Responses to “Team Generator in excel”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

1 - I see you could change the formula to have the experssion COUNTIF($C$1:C1, $E$2:$E$5)<5 changed so the maximum number of team members looks to a cell (say E9) so the number of people per team could be easily changed eg COUNTIF($C$1:C1, $E$2:$E$5)<E9

2 - In (virtual) sailing team racing, competition is usually 3 vs 3, but could be adjusted to a 4 vs 4, 3 vs 2 or 4 vs 3 depending on the number of skippers available to race. Could the example be modified to assign a skipper to a team given these optimum arrangements? (eg for say 12 skippers entered it would do 2 sets of 3 vs 3, for 8 skippers it would do one 4 vs 4, etc)

Mark G,

read this post: Dynamic team generator in excel

I have a question that may be more complex, but similar in theory.

This is for a team building exercise in my classroom.

I have 18 students. I want to generate groups of 3 that will allow each person to work with 2 unique people each time, with no repeats. I think this would mean 8 groups of 3, and 1 group of 2. Or, 7 groups of 3, and 1 group of 4.

Does this make sense? Can anyone help?

I play a vball league where We'd like to have a spreadsheet that we plug the 16 names (8 guys + 8 girls) into, and it produces randomly generated teams based on the following:

1. We play 8 games

1. 2 guys and 2 girls per team

2. Cannot be paired with any another player more than 3 times.

3. It would be nice to be paired with all other players at least once during the 8 games.