## Create a random playlist

### Overview

This article describes how to create a random playlist of four teams total.

Column A contains four teams.

Each team plays matches both "Home" against all remaining teams and "Away" against all remaining teams.

*Example:*

Team A plays "Home" against Team B, Team C and Team D.

Team A- Team B

Team A - Team C

Team A - Team D

Team A plays also "Away" against Team B, Team C and Team D.

Team B - Team A

Team C - Team A

Team D - Team A

Column C and Column D contains a random playlist. Press F9 to refresh.

### Array formulas

Array formula in C2:

**3**)*ROW($1:$

**4**), RANDBETWEEN(1, SUMPRODUCT(--(COUNTIF($C$1:C1, $A$2:$A$5)<

**3**)))))

+ CTRL + SHIFT + ENTER. Copy cell C2 and paste it down as far as needed.

If column A contains five teams, change ROW($1:$**4**) to ROW($1:$**5**) and bolded **3**´s to **4** in the above formula. Don´t forget to adjust cell references.

Array formula i D2:

**3**)*(COUNTIF(C2, $A$2:$A$5)=0)*(ISERROR(MATCH($A$2:$A$5, IF(C2=$C$1:C1, $D$1:D1, ""), 0)))*ROW($1:$

**4**), RANDBETWEEN(1, SUMPRODUCT(--((ISERROR(MATCH($A$2:$A$5, IF(C2=$C$1:C1, $D$1:D1, ""), 0)))*(COUNTIF($D$1:D1, $A$2:$A$5)<

**3**)*(COUNTIF(C2, $A$2:$A$5)=0))))))

+ CTRL + SHIFT + ENTER. Copy cell D2 and paste it down as far as needed.

If column A contains five teams, change ROW($1:$**4**) to ROW($1:$**5**) and bolded **3**´s to **4** in the above formula. Don´t forget to adjust cell references.

### Download excel sample file for this tutorial

random plays.xls

(Excel 97-2003 Workbook *.xls)

### Functions in this article:

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

**ISERROR(**value)

Checks whether a value is an error and returns TRUE or FALSE

**MATCH(**lookup_value;lookup_array; [match_type]**)**

Returns the relative position of an item in an array that matches a specified value

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)

Introduction This article describes how to randomly select 6 unique numbers from 1 to 49. In a 6-from-49 lotto, a […]

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

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

Sort a list in random order in excel

Overview The array formula in cell range C2:C6 creates a random list from the values in cell range A2:A6. Array […]

Assign each person with randomly unique objects as a daily activity

Vijay asks: I've 30 objects and 10 people, and I need to assign each person with randomly (unique) selected objects […]

How to create random numbers, text strings, dates and time values

In excel the RAND() function returns a number greater than or equal to 0 (zero) and less than 1. Combining […]

Generate list of random dates with criterion

Overview Today we are going to build an array formula. Step by step creating random dates with a criterion. The criterion […]

### 10 Responses to “Create a random playlist”

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

Can this example be modified to create a true round robin draw? i.e. using a 6 team scenario, for any round there will be three games/matches, but obviously Team A could not compete against say Team B and Team C during that round as can occur using the above formula.

This is a really good question. I hope I can post an answer soon.

Thanks for commenting!

Mark G,

See this post: Create a random playlist in excel – True round-robin tournament

Hi,

I have a question?

I am tryng to list the people with the highest scores based on certain criteria.

My data:

column

A B C D

Mike 207 Yes Life

Greg 207 Yes Life

Sid 207 Yes Life

Greg 207 Yes Life

Greg 207 Yes Life

Sid 207 Yes Life

Greg 207 No Life

Sid 204 No Health

criteria

countif b= 207 and column c= yes and column d= Life

and it then needs to arange it from the highest to the lowest

(Large)

and then match it with the name eg, Greg Sid or Mike

so what i am looking for is eg.

Greg 3

Sid 2

Mike 1

but it has to be in one formula.

can you maybe help?

Andre.

Andre,

Read this post: List people with the highest scores based on criteria in excel

Hi Oscar

I have downloaded your home & away generator for 4 teams which is eactly what I am looking for. Do you however have a similar spreadsheet for 3, 5 and 6 teams? If you do please could you e-mail me links?

Cheers

Dale

Dale,

See attached file:

random-plays-teams.xls

You are a star. I need to insert some rows at the top but it seems to muck up the formulas. Is there any easy way to fix this?

Cheers

Dale

Dale,

I think you´ll have to move the cell references accordingly in both formulas.

Hi Oscar,

I am looking for a tournement-schedule with the following criteria:

16 tennis players

26 weeks (=13 matches) and/or 24 weeks (=12 matches)

2 courts with 4 teams (1 and 2 against 3 and 4 / 5 and 6 against 7 and 8)

Preferences:

intention to play once in 2 weeks

intention to play with the same player 2 times at most

intention to play against the same player 2 times at most

each player is playing 13 (26 weeks) or 12 (24 weeks) times

I hope there is a possibilty to create such a scheduler.

Regards,

Jacques