## Dynamic team generator

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)

**Answer:**

The image above demonstrates a worksheet containing Team names in an Excel defined table cell range F3:F7, you don't need to adjust cell references in the formula if you add or delete team names. It is dynamic and updates instantly.

Enter the number of members in each team in cell H3, the formula refreshes automatically. It randomly assigns teams to players, press F9 to refresh the formula output.

Array formula in cell D3:

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Copy cell D3 and paste it down as far as necessary.

### Explaining formula in cell D3

#### Step 1 - Count previous values

The COUNTIF function counts values based on a condition or criteria, the first argument contains an expanding cell reference, it grows when the cell is copied to cells below. This makes the formula aware of values displayed in cells above. 0 (zero) indicates values that not yet have been displayed.

COUNTIF($D$2:D2, Table1[Teams])<$H$3

becomes

COUNTIF("Team", {"Team A"; "Team B"; "Team C"; "Team D"; "Team E"})<$H$3

becomes

{0;0;0;0;0}<3

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE}

#### Step 2 - Create row numbers

This step is needed to create a sequence of numbers that will be used to get the correct value in a later step.

MATCH(ROW(Table1[Teams]),ROW(Table1[Teams]))

becomes

MATCH(ROW({"Team A"; "Team B"; "Team C"; "Team D"; "Team E"}),ROW({"Team A"; "Team B"; "Team C"; "Team D"; "Team E"}))

becomes

MATCH({3;4;5;6;7}, {3;4;5;6;7})

and returns {1;2;3;4;5}.

#### Step 3 - Multiply arrays

(COUNTIF($D$2:D2, Table1[Teams])<$H$3)*(MATCH(ROW(Table1[Teams]),ROW(Table1[Teams])))

becomes

{TRUE; TRUE; TRUE; TRUE; TRUE}* {1;2;3;4;5}

and returns {1;2;3;4;5}.

#### Step 4 - Extract random row number

The LARGE function returns the k-th largest number, LARGE( array , k). The second argument k is a random number from 1 to n.

LARGE((COUNTIF($D$2:D2, Table1[Teams])<$H$3)*(MATCH(ROW(Table1[Teams]),ROW(Table1[Teams]))), RANDBETWEEN(1, SUMPRODUCT(--(COUNTIF($D$2:D2, Table1[Teams])<$H$3))))

becomes

LARGE({1;2;3;4;5}, RANDBETWEEN(1, SUMPRODUCT(--(COUNTIF($D$2:D2, Table1[Teams])<$H$3))))

becomes

LARGE({1;2;3;4;5}, RANDBETWEEN(1, SUMPRODUCT({1; 1; 1; 1; 1})))

becomes

LARGE({1;2;3;4;5}, RANDBETWEEN(1, 5))

becomes

LARGE({1;2;3;4;5}, 2)

and returns 2. This is a random number between 1 and 5.

#### Step 5 - Return value

The INDEX function returns a value based on a cell reference and column/row numbers.

INDEX(Table1[Teams], LARGE((COUNTIF($D$2:D2, Table1[Teams])<$H$3)*(MATCH(ROW(Table1[Teams]),ROW(Table1[Teams]))), RANDBETWEEN(1, SUMPRODUCT(--(COUNTIF($D$2:D2, Table1[Teams])<$H$3)))))

becomes

INDEX(Table1[Teams], 2)

and returns "Team B" in cell D3.

#### Step 6 - Trap errors

The formula returns errors when all teams are populated, the IFERROR function converts the errors into a given value, in this case "-".

IFERROR(INDEX(Table1[Teams], LARGE((COUNTIF($D$2:D2, Table1[Teams])<$H$3)*(MATCH(ROW(Table1[Teams]),ROW(Table1[Teams]))), RANDBETWEEN(1, SUMPRODUCT(--(COUNTIF($D$2:D2, Table1[Teams])<$H$3))))), "-")

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. [โฆ]

### 9 Responses to โDynamic 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

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

The is a fabulous tool! I used it for generating business simulation teams for a leadership development exercise at work. It worked great! Thank you so much!

Lisa Liszcz,

Thank you, I am happy you like it!

Great post, worked really well. Used it in business competition teams assignments. Appreciate sharing the knowledge, thanks so much !

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?

JD

Read this post: https://www.get-digital-help.com/2015/04/22/team-generator/

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?

What if one of those names were to be absent, how would you write the code if say cell B4 was left blank an the generator didn't count them on a team?

Great tool! I'm trying to understand how the formula works. Could someone explain what this part of the formula does?

COUNTIF($C$1:C1, OFFSET($D$2:$D$5, 0, 0, $F$1))<$F$2

I understand what the COUNTIF and OFFSET commands do, but I don't get how they are being used in this part of the formula

Hi Oscar,

Do you have a template for 60 people to be randomly generated into teams of 4?

Thanks,

Vlad