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

random plays.xls
(Excel 97-2003 Workbook *.xls)

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