Help with a fighting simulation

[Auranim]

Hi! I'm developing a system in which I need to pit participants against each other. For that, I have a list with 4 participants like such

# | Participant name
1 | John
2 | Michael
3 | Erik
4 | Dennis

I need to create a table that automatically shows each match, as such:

John | v.s.| Michael
John | v.s. | Erik
John | v.s. | Dennis
Michael | v.s. | Erik
Michael | v.s. | Dennis
Erik | v.s. | Dennis

I've been doing this manually but sometimes there will be more participants or fewer participants, so it would be great to create two formulas I can extend all the way down my spreadsheet that encompasses every possible match. The order of the matches doesn't matter, the only thing that matters is, in the end, every participant must have fought everyone else only once.

 

[Auranim]

Sorry, it is my mistake...i forgot -1 for the formula in D2. please test below formulas:

Book1.xlsx
	A	B	C	D	E	F
1	Participant name		helper	Participant name1		Participant name2
2	John		2	John	| v.s.|	Michael
3	Michael		2	John	| v.s.|	Erik
4	Erik		2	John	| v.s.|	Dennis
5	Dennis		2	John	| v.s.|	Test
6	Test		3	Michael	| v.s.|	Erik
7			3	Michael	| v.s.|	Dennis
8			3	Michael	| v.s.|	Test
9			4	Erik	| v.s.|	Dennis
10			4	Erik	| v.s.|	Test
11			5	Dennis	| v.s.|	Test
Sheet5

Cell Formulas
Range	Formula
C2:C11	C2	=IFERROR(SMALL(IF(TRANSPOSE(ROW(Table1[Participant name]))>ROW(Table1[Participant name]),ROW(Table1[Participant name]),""),ROW(A1)),"")
D2:D11	D2	=IFERROR(INDEX(Table1[Participant name],C2-1),"")
E2:E11	E2	=IF(D2<>"","| v.s.|","")
F2:F11	F2	=IFERROR(INDEX(Table1[Participant name],C2+COUNTIF(C$1:C1,C2)),"")

One last thing. Is there any way to do with using cell ranges instead of dynamic tables?

 

[shaowu459]

yes, OFFSET is OK.

Book1.xlsx
	A	B	C	D	E	F
1	Participant name		helper	Participant name1		Participant name2
2	John		2	John	| v.s.|	Michael
3	Michael		2	John	| v.s.|	Erik
4	Erik		2	John	| v.s.|	Dennis
5	Dennis		2	John	| v.s.|	Test
6	Test		3	Michael	| v.s.|	Erik
7			3	Michael	| v.s.|	Dennis
8			3	Michael	| v.s.|	Test
9			4	Erik	| v.s.|	Dennis
10			4	Erik	| v.s.|	Test
11			5	Dennis	| v.s.|	Test
12
13
14
15
16
17
18
19
20
21
22
Sheet5

Cell Formulas
Range	Formula
C2:C20	C2	=IFERROR(SMALL(IF(TRANSPOSE(ROW(OFFSET($A$2,,,COUNTA(A:A)-1,)))>ROW(OFFSET($A$2,,,COUNTA(A:A)-1,)),ROW(OFFSET($A$2,,,COUNTA(A:A)-1,)),""),ROW(A1)),"")
D2:D20	D2	=IFERROR(INDEX(OFFSET($A$2,,,COUNTA(A:A)-1,),C2-1),"")
E2:E20	E2	=IF(D2<>"","| v.s.|","")
F2:F20	F2	=IFERROR(INDEX(OFFSET($A$2,,,COUNTA(A:A)-1,),C2+COUNTIF(C$1:C1,C2)),"")

 

[Auranim]

Huh, that's odd. Those ones didn't work for me. They just give me blank cells :/

 

[shaowu459]

Huh, that's odd. Those ones didn't work for me. They just give me blank cells :/

are you using office 365? if not, use CTRL+SHIFT+ENTER for the array formula in column C.

 

[Auranim]

are you using office 365? if not, use CTRL+SHIFT+ENTER for the array formula in column C.

I am using 365. What I did was install Excel in English (not my first language). Soon as I did it, it worked :P Thanks a lot hahaha for the formulas and for the patience! <3

 

[Peter_SSs]

Another option you could try without the volatile function OFFSET & some simpler formulas.
You can, of course, hide the helper column once it has its formulas

Auranim 2020-05-17 1.xlsm
	A	B	C	D	E	F	G
1	Participant name			4	Participant 1		Participant 2
2	John			1	John	|v.s.|	Michael
3	Michael			1	John	|v.s.|	Erik
4	Erik			1	John	|v.s.|	Dennis
5	Dennis			2	Michael	|v.s.|	Erik
6				2	Michael	|v.s.|	Dennis
7				3	Erik	|v.s.|	Dennis
8
9
10
11
12
13
Sheet1

Cell Formulas
Range	Formula
D1	D1	=COUNTA(A:A)-1
D2:D13	D2	=IF(D1=D$1,1,IF(LOOKUP(D$1,D$1:D1)=D$1-1,"",IF(COUNTIF(D$1:D1,D1)<=D$1-COUNT(UNIQUE(D$1:D1)),D1,D1+1)))
E2:E13	E2	=IF(D2="","",INDEX(A:A,D2+1))
F2:F13	F2	=IF(D2="","","|v.s.|")
G2:G13	G2	=IF(D2="","",INDEX(A:A,D2+1+COUNTIF(D$2:D2,D2)))

After adding an extra participant:

Auranim 2020-05-17 1.xlsm
	A	B	C	D	E	F	G
1	Participant name			5	Participant 1		Participant 2
2	John			1	John	|v.s.|	Michael
3	Michael			1	John	|v.s.|	Erik
4	Erik			1	John	|v.s.|	Dennis
5	Dennis			1	John	|v.s.|	Simon
6	Simon			2	Michael	|v.s.|	Erik
7				2	Michael	|v.s.|	Dennis
8				2	Michael	|v.s.|	Simon
9				3	Erik	|v.s.|	Dennis
10				3	Erik	|v.s.|	Simon
11				4	Dennis	|v.s.|	Simon
12
13
Sheet1

 

[Auranim]

Another option you could try without the volatile function OFFSET & some simpler formulas.
You can, of course, hide the helper column once it has its formulas

Auranim 2020-05-17 1.xlsm
	A	B	C	D	E	F	G
1	Participant name			4	Participant 1		Participant 2
2	John			1	John	|v.s.|	Michael
3	Michael			1	John	|v.s.|	Erik
4	Erik			1	John	|v.s.|	Dennis
5	Dennis			2	Michael	|v.s.|	Erik
6				2	Michael	|v.s.|	Dennis
7				3	Erik	|v.s.|	Dennis
8
9
10
11
12
13
Sheet1

Cell Formulas
Range	Formula
D1	D1	=COUNTA(A:A)-1
D2:D13	D2	=IF(D1=D$1,1,IF(LOOKUP(D$1,D$1:D1)=D$1-1,"",IF(COUNTIF(D$1:D1,D1)<=D$1-COUNT(UNIQUE(D$1:D1)),D1,D1+1)))
E2:E13	E2	=IF(D2="","",INDEX(A:A,D2+1))
F2:F13	F2	=IF(D2="","","|v.s.|")
G2:G13	G2	=IF(D2="","",INDEX(A:A,D2+1+COUNTIF(D$2:D2,D2)))

After adding an extra participant:

Auranim 2020-05-17 1.xlsm
	A	B	C	D	E	F	G
1	Participant name			5	Participant 1		Participant 2
2	John			1	John	|v.s.|	Michael
3	Michael			1	John	|v.s.|	Erik
4	Erik			1	John	|v.s.|	Dennis
5	Dennis			1	John	|v.s.|	Simon
6	Simon			2	Michael	|v.s.|	Erik
7				2	Michael	|v.s.|	Dennis
8				2	Michael	|v.s.|	Simon
9				3	Erik	|v.s.|	Dennis
10				3	Erik	|v.s.|	Simon
11				4	Dennis	|v.s.|	Simon
12
13
Sheet1

There we go. It works 100% the way I needed! Brilliant, thank you SO much! <3

 

[Peter_SSs]

There we go. It works 100% the way I needed! Brilliant, thank you SO much! <3

You're welcome. Thanks for the follow-up.

 

[shaowu459]

use simpler formulas for no more than 7 participants.

Book1.xlsx
	A	B	C	D	E	F
1	Participant name		helper	Participant name1		Participant name2
2	John		5	Dennis	| v.s.|	Test
3	Michael		4	Erik	| v.s.|	Dennis
4	Erik		4	Erik	| v.s.|	Test
5	Dennis		3	Michael	| v.s.|	Erik
6	Test		3	Michael	| v.s.|	Dennis
7			3	Michael	| v.s.|	Test
8			2	John	| v.s.|	Michael
9			2	John	| v.s.|	Erik
10			2	John	| v.s.|	Dennis
11			2	John	| v.s.|	Test
12
13
14
15
16
17
18
19
20
Sheet5

Cell Formulas
Range	Formula
C2:C20	C2	=TEXT(COUNTA(A:A)-ROUND(SQRT(ROW(A1)*2),),"[>1]0;;")
D2:D20	D2	=IF(C2="","",INDEX(A:A,C2))
E2:E20	E2	=IF(D2<>"","| v.s.|","")
F2:F20	F2	=IF(C2="","",INDEX(A:A,C2+COUNTIF(C$2:C2,C2)))

 

[Peter_SSs]

For any number of participants (provided formulas are copied down far enough) with simple formulas. Assuming that you would hide the helper column.

Auranim 2020-05-17 1.xlsm
	A	B	C	D	E	F	G
1	Participant name			10	Participant 1		Participant 2
2	Player 1			9	Player 10	|v.s.|	Player 9
3	Player 2			9	Player 10	|v.s.|	Player 8
4	Player 3			9	Player 10	|v.s.|	Player 7
5	Player 4			9	Player 10	|v.s.|	Player 6
6	Player 5			9	Player 10	|v.s.|	Player 5
7	Player 6			9	Player 10	|v.s.|	Player 4
8	Player 7			9	Player 10	|v.s.|	Player 3
9	Player 8			9	Player 10	|v.s.|	Player 2
10	Player 9			9	Player 10	|v.s.|	Player 1
11	Player 10			8	Player 9	|v.s.|	Player 8
12				8	Player 9	|v.s.|	Player 7
13				8	Player 9	|v.s.|	Player 6
14				8	Player 9	|v.s.|	Player 5
15				8	Player 9	|v.s.|	Player 4
16				8	Player 9	|v.s.|	Player 3
17				8	Player 9	|v.s.|	Player 2
18				8	Player 9	|v.s.|	Player 1
19				7	Player 8	|v.s.|	Player 7
20				7	Player 8	|v.s.|	Player 6
21				7	Player 8	|v.s.|	Player 5
22				7	Player 8	|v.s.|	Player 4
23				7	Player 8	|v.s.|	Player 3
24				7	Player 8	|v.s.|	Player 2
25				7	Player 8	|v.s.|	Player 1
26				6	Player 7	|v.s.|	Player 6
27				6	Player 7	|v.s.|	Player 5
28				6	Player 7	|v.s.|	Player 4
29				6	Player 7	|v.s.|	Player 3
30				6	Player 7	|v.s.|	Player 2
31				6	Player 7	|v.s.|	Player 1
32				5	Player 6	|v.s.|	Player 5
33				5	Player 6	|v.s.|	Player 4
34				5	Player 6	|v.s.|	Player 3
35				5	Player 6	|v.s.|	Player 2
36				5	Player 6	|v.s.|	Player 1
37				4	Player 5	|v.s.|	Player 4
38				4	Player 5	|v.s.|	Player 3
39				4	Player 5	|v.s.|	Player 2
40				4	Player 5	|v.s.|	Player 1
41				3	Player 4	|v.s.|	Player 3
42				3	Player 4	|v.s.|	Player 2
43				3	Player 4	|v.s.|	Player 1
44				2	Player 3	|v.s.|	Player 2
45				2	Player 3	|v.s.|	Player 1
46				1	Player 2	|v.s.|	Player 1
47				0
48				0
Sheet3

Cell Formulas
Range	Formula
D1	D1	=COUNTA(A:A)-1
D2:D48	D2	=D1-OR(COUNTIF(D$1:D1,D1)=D1,D1=D$1)
E2:E48	E2	=IF(D2=0,"",INDEX(A:A,D2+2))
F2:F48	F2	=IF(E2="","","|v.s.|")
G2:G48	G2	=IF(F2="","",INDEX(A:A,D2+2-COUNTIF(D$2:D2,D2)))