## Dynamic team generator in excel

*Article updated on January 24, 2018*

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

**Array formula in cell C2:**

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

Change values in cell F1 and F2 to create a different team setup.

### Download excel sample file for this tutorial

Dynamic Team Generator.xls

(Excel 97-2003 Workbook *.xls)

### Functions in this article:

**OFFSET(**reference,rows,cols, [height],[width]**)**

Returns a reference to a range that is a given number of rows and columns from a given reference

**SUMPRODUCT(**array1, array2, **)**

Returns the sum of the products of the corresponding ranges or arrays

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

**LARGE(**array,k**)**

Returns the k-th largest row number in this data set.

**ROW(**reference**)** returns the rownumber of a reference

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**RANDBETWEEN(**bottom, top**)**

Returns a random number between the numbers you specify

Extract unique distinct values from a multi-column cell range

Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]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 […]### 9 Responses to “Dynamic 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

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