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

How to create a list of random unique numbers in excel

Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and […]Comments(102) Filed in category: Excel, Random

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 […]Comments(16) Filed in category: Excel, Random

Dynamic team generator in excel

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, Team generator

Comments(7) Filed in category: Excel, Random, Team generator

Comments(4) Filed in category: Excel, Random, Team generator

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 […]Comments(4) Filed in category: Excel, Random, Sort values

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 […]Comments(3) Filed in category: Excel, Random, Unique distinct values

Generate list of random dates with criterion in excel

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

Random row from a boolean matrix in excel

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