## Team Generator in excel

*Article updated on August 25, 2017*

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 the SUMPRODUCT function

The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.

How to use the COUNTIF function

Counts the number of cells that meet a specific condition.

The SMALL function lets you extract a number in a cell range based on how small it is compared to the other numbers in the group.

Gets a value in a specific cell range based on a row and column number.

How to generate random numbers and text

What's on this page Random numbers with a decimal Random negative numbers with two decimals Random negative numbers with three […]

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 […]### 10 Responses to “Team Generator in excel”

### Leave a Reply

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

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**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

Hi,

I plan to use something like this so people can sign up for football on a Google sheet. Is it possible to get a team member assigned a team at random as they sign up?

So 2 teams of 7, assigned Team A or B as they sign up, and also prevent it from recalculating on change.

Cheers!

Oliver

Hi Oliver

Is it possible to get a team member assigned a team at random as they sign up? So 2 teams of 7, assigned Team A or B as they sign up, and also prevent it from recalculating on change.I believe you need a macro to do that.

Hi,

How to do this without being random?

What I mean is, I am looking to divide a bunch of soccer players in 3 teams. Each player has a rating, position he pays for and availability as parameters.

Now how can I distribute them evenly in each team so that all the skill-sets and ratings of all the available players are distributed evenly?

The flow for it is as follows:

1) First it needs to check availability

2) Sort the all the Goalkeepers in 3 teams

3) Then all strikers and then all defenders to be distributed

Every week for a season, the only input that will change will be availability rest everything will be same.

Sorry for asking such a lengthy question, but have been trying to solve it form a really long time.