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:

=INDEX($A$2:$A$5, LARGE((COUNTIF($C$1:C1, $A$2:$A$5)<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:

=INDEX($A$2:$A$5, LARGE((COUNTIF($D$1:D1, $A$2:$A$5)<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

Related posts:

Create a random playlist in excel – True round-robin tournament

Team Generator in excel

How to create a list of random unique numbers in excel

Create random dates, Mon to Fri, within a year in excel

Dynamic team generator in excel