## Create a random playlist

*Article last updated on January 24, 2018*

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

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

5 easy ways to extract unique distinct values

You have quite a few options to choose from if you are looking for a way to create a unique […]

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

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

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

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