## 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.

**5**)*(ROW($1:$

**4**)), RANDBETWEEN(1, SUMPRODUCT(--(COUNTIF($C$1:C1, $E$2:$E$5)<

**5**)))))

This article demonstrates how to generate teams with different sizes:

JD asks in this post: Dynamic team generator Hi, what if we have different number of people per team? So in […]

The following shows you how to setup a dynamic scoreboard, enter results and the scoreboard is automatically recalculated:

Here comes another post about the MMULT function, today I made a dynamic scoreboard. There are five women competing and there […]

### How to create an array formula

- Copy the aray formula above (Ctrl + c)
- Double click cell B2
- Paste (Ctrl + v)
- Press and hold Ctrl + Shift simultaneously
- Press Enter
- Release all keys

If you made the above steps correctly the formula now has a beginning and ending curly bracket, like this:

{=*array_formula*}

Don't enter these characters yourself, they appear automatically.

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

### How to copy formula

Copy cell C2 and paste it down.

### Customize formula

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:

How to use Excel SUMPRODUCT function

Multiplies cell ranges and then sum all values.

Learn to use the COUNTIF function

Counts the number of times a value exists in a cell range.

SMALL function and LARGE function

This function lets you extract any number in a cell range based on sort rank.

Fetch a value in a data set based on coordinates.

Excel RANDBETWEEN and RAND functions – How to generate random numbers and text

Microsoft Excel has two useful functions for generating numbers. The RAND function and RANDBETWEEN function. RAND() function returns a random […]

Select 6 unique and random numbers from 1 to 49 (Lottery) in excel

Introduction This article describes how to randomly select 6 unique numbers from 1 to 49. In a 6-from-49 lotto, a […]### 7 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.

[…] }); Check the below links.. Hope it might give you a solution. Team Generator in excel | Get Digital Help - Microsoft Excel resource […]

How would you change the name list to be dynamic such as 20 one time then 43 another and so on and then make teams of 4 with odd leftovers in a team ? Of course, no limit on number of teams just whatever it takes.

Hello,

I am trying to adjust the formula to have people for 6 teams. There are actually three teams of 2, but I want to assign specific positions for each team, so I am making it 6 teams in excel. For example, Team one would have a forward and a goalie, so in excel I am making it 2 teams, G1 and F1. I want to have 6 people, 6 teams, so everyone is assigned a position and team. I keep trying to edit the formula to let me do this but I keep getting #NUM! in the team assignment sections. What could I be doing wrong?

I change the COUNTIF($C$1:C1, $E$2:$E$5)<5 to <1

And I change the ROW($1:$4) to ROW($1:$6)

Any suggestions?

Thanks