## Team generator

JD asks in this post: Dynamic team generator

Yes, there is. This array formula uses the values in cell range D2:E4 to randomly assign people to teams, demonstrated in column B and C.

### Array formula in cell C2

This array formula contains a couple of excel functions, if you want to know more about them check out these posts: LARGE, INDEX, COUNTIF, IFERROR, MATCH, ROW, RANDBETWEEN. It also has both absolute and relative cell references, learn more.

### How to enter an array formula

- Copy above array formula
- Select cell C2
- Paste array formula to formula bar
- Press and hold CTRL + SHIFT simultaneously
- Press Enter once

If you did it right the formula now begins and ends with curly brackets, like this {=array formula}. Don't enter the curly brackets yourself.

### How to copy formula to cells below

- Select cell C2
- Copy cell (Shortcut key CTRL + c)
- Select cell range C3:C21
- Paste (Shortcut key CTRL + v)

You can also select cell C2, click and hold on the black dot on the lower right corner of cell C2. Drag it down as far as needed.

### Named ranges in array formula

There are two named ranges in this worksheet, *Teams* and *Members*. They return a cell range in column D and E respectively that grow as you add new values.

Teams:

Members:

### Disable automatic recalculation

The RANDBETWEEN function is volatile and each time you enter a new value somewhere on this sheet, array formulas are recalculated.

To prevent this from happening the following event code turns off automatic calculation. Activating sheet1 enables manual calculation, deactivating sheet1 enables automatic calculation.

Private Sub Worksheet_Activate() Application.Calculation = xlCalculationManual End Sub Private Sub Worksheet_Deactivate() Application.Calculation = xlCalculationAutomatic End Sub

**Where is this code? **Right click on tab sheet1. Click on "View Code..."

Remember to press F9 after editing teams and member columns.

Want to learn more about advanced excel techniques, join my Advanced excel course.

### Download excel *.xlsm file

These posts use the same technique:

How to create a list of random unique numbers

Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and […]

This article describes how to create a random playlist based on a given number of teams using an array formula. […]

This blog article describes how to create teams randomly. There are twenty names in column B and four teams in […]

Mark G asks: 1 - I see you could change the formula to have the experssion COUNTIF($C$1:C1, $E$2:$E$5)<5 changed so […]

### 5 Responses to “Team generator”

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

In a scenario like this and recalculating the setup several times, I see that the Team A is never assigned to #10 or greater. Is this a limitation?

Felipe Costa Gualberto,

This workbook assigns teams evenly.

https://www.get-digital-help.com/wp-content/uploads/2015/04/Team-Generatorv2.xlsm

I'm trying to upload the image, but I can't.

The link would be: https://s24.postimg.org/iy5s86r8l/Sem_t_tulo.png

However, the setup I did was just 1 member in Team A and 19 members in Team B.

Felipe Costa Gualberto,

If there are two teams, the probability is 50% that a team is displayed in a cell, that is why Team B is shown almost always in one of the three or four first cells. This is perhaps not ideal, I am working on a better formula.

Hi Oscar, how would you do this with an additional level of criteria? For example, if your participants are also in categories: A, B & C and in Group 1 you want 3 Category A participants, 2 Category B, etc.