Creating a Fixture List

[oakwood_001]

Column A	Column B	Column C
Player 1	vs.	Player 2
Player 1	vs.	Player 3
Player 1	vs.	Player 4
Player 1	vs.	Player 5
Player 2	vs.	Player 3
etc.

<tbody>
</tbody>

Hi All,

I want to create a fixture table from a list of players (eachplayer in a separate cell in one column) where each player will be matched (in two separatecolumns) against a unique player. It hasto recognise a match both ways no matter which player comes first – i.e. Player1 vs. Player 2 is the same as Player 2 vs. Player 1 – and so that match willonly appear once.


I don’t want to use VBA or a Macro to do this but I can’twork out how to using formulas. I want to accommodate up to 10 unique players, but only want the formula to apply if a cell is not empty (i.e. <>"").


e.g.

My list of players:
Player 1
Player 2
Player 3
Player 4
Player 5

Turns into a table similar to the above (sorry couldn’tmove the table downwards).

Any help would be greatly appreciated.

Thanks.

 

[Eric W]

I'm working on a bit more elegant set of formulas, but for now, try:

	A	B	C	D	E
1	Column A	Column B	Column C		Players
2	Andy	vs	Beth		Andy
3	Andy	vs	Chuck		Beth
4	Andy	vs	Donna		Chuck
5	Andy	vs	Eric		Donna
6	Beth	vs	Chuck		Eric
7	Beth	vs	Donna
8	Beth	vs	Eric
9	Chuck	vs	Donna
10	Chuck	vs	Eric
11	Donna	vs	Eric

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet6​

Worksheet Formulas Cell Formula A3 =IF(A2="","",IF(COUNTIF($A$2:$A2,A2)<COUNTA($E$2:$E$11)-MATCH(A2,$E$2:$E$11,0),A2,IF(MATCH(A2,$E$2:$E$11,0)=COUNTA($E$2:$E$11)-1,"",INDEX($E$3:$E$12,MATCH(A2,$E$2:$E$11,0))))) A2 =E2 B2 =IF(A2="","","vs") C2 =E3 C3 =IF(A3="","",INDEX($E$2:$E$11,COUNTIF($A$2:$A3,A3)+MATCH(A3,$E$2:$E$11,0))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Eric W]

If you're willing to use a helper column (which you can hide), then the A formula can be simplified to:

	A	B	C	D	E	F
1	Column A	Column B	Column C		Players
2	Andy	vs	Beth		Andy	0
3	Andy	vs	Chuck		Beth	5
4	Andy	vs	Donna		Chuck	9
5	Andy	vs	Eric		Donna	12
6	Andy	vs	Felice		Eric	14
7	Beth	vs	Chuck		Felice	15
8	Beth	vs	Donna			15
9	Beth	vs	Eric			15
10	Beth	vs	Felice			15
11	Chuck	vs	Donna			15
12	Chuck	vs	Eric			15
13	Chuck	vs	Felice			15
14	Donna	vs	Eric			15
15	Donna	vs	Felice			15
16	Eric	vs	Felice			15
17						15

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet6​

Worksheet Formulas Cell Formula A2 =INDEX($E$2:$E$11,MATCH(ROWS($A$2:$A2)-1,$F$2:$F$11))&"" B2 =IF(A2="","","vs") C2 =E3 C3 =IF(A3="","",INDEX($E$2:$E$11,COUNTIF($A$2:$A3,A3)+MATCH(A3,$E$2:$E$11,0))) F3 =F2+COUNTA(E3:E$11) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Still thinking about the C formula.

 

[Eric W]

In hindsight C2 can be:

=IF(A2="","",INDEX($E$2:$E$11,COUNTIF($A$2:$A2,A2)+MATCH(A2,$E$2:$E$11,0)))

and copy down. No need for the special =E3 formula in C2.