## Create a random playlist in excel

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

### Category: Random

Table of Contents Generate unique random numbers Generate unique random values from a cell range Generate unique random numbers Question: […]

Comments(102) Filed in category: Excel, Random, Unique values

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

Comments(16) Filed in category: Excel, Random

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

Comments(9) Filed in category: Excel, Random

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

Comments(5) Filed in category: Excel, Random

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

Comments(4) Filed in category: Excel, Random

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

Comments(4) Filed in category: Excel, Random, Sort values

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

Comments(3) Filed in category: Excel, Random, Unique distinct values

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

Comments(2) Filed in category: Dates, Excel, Random

Question: It’s the kind of thing I do regularly in matrix oriented languages (APL, K, Gauss etc), but I can’t […]

Comments(0) Filed in category: Excel, Random

### 10 Responses to “Create a random playlist 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

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