Author: Oscar Cronquist Article last updated on August 25, 2017

JD asks in this post: Dynamic team generator

Hi, what if we have different number of people per team? So in team A, there could be a max of 4 members but in team B there could be a max of 6 members etc. Is there some formula which can be used for this as well?

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

=IFERROR(INDEX(\$D\$2:\$D\$18,LARGE((COUNTIF(\$C\$1:C1,Teams)<>Members)*MATCH(ROW(Teams),ROW(Teams)),RANDBETWEEN(1,SUM(--(COUNTIF(\$C\$1:C1,Teams)<>Members))))),"")

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

1. Copy above array formula
2. Select cell C2
3. Paste array formula to formula bar
4. Press and hold CTRL + SHIFT simultaneously
5. 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

1. Select cell C2
2. Copy cell (Shortcut key CTRL + c)
3. Select cell range C3:C21
4. 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:

=Sheet1!\$D\$2:INDEX(Sheet1!\$D\$2:\$D\$100,COUNTA(Sheet1!\$D\$2:\$D\$100))

Members:

=Sheet1!\$E\$2:INDEX(Sheet1!\$E\$2:\$E\$100,COUNTA(Sheet1!\$D\$2:\$D\$100))

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