## Dynamic team generator in excel

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

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

Find latest date based on a condition

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]

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 […]

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. […]

Calculate average of last 10 data with possible blank cells

Question: Answer: This array formula creates a dynamic range, filtering the 10 last data. Adjust cell rangesÂ $A$1:$A$25 in formula below. […]

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 […]

Overview This article describes how to create a random playlist of four teams total. Column A contains four teams. Each […]

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 […]

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 […]

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 […]

Overview This article describes how to create a random playlist of four teams total. Column A contains four teams. Each […]

Formula for matching a date within a date range

Table of contents Match a date when a date range is entered in a single cell Match a date when […]

Identify overlapping date ranges

The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]

Overview This article describes how to create a random playlist of four teams total. Column A contains four teams. Each […]

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

JD asks in this post:Â Dynamic team generator Hi, what if we have different number of people per team? So in […]

### 9 Responses to “Dynamic team generator in excel”

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

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