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.

 

[shaowu459]

interesting question, i come up with an OFFICE 365 solution. the formula is a little bit long, because it referece to a TABLE, the formula will return expected result automattically when you add or remove items in TABLE.

Book1.xlsx
	A	B	C
1	Participant name		John | v.s.| Michael
2	John		John | v.s.| Erik
3	Michael		John | v.s.| Dennis
4	Erik		Michael | v.s.| Erik
5	Dennis		Michael | v.s.| Dennis
6			Erik | v.s.| Dennis
7
8
9
10
11
Sheet5

Cell Formulas
Range	Formula
C1:C11	C1	=TRIM(MID(SUBSTITUTE(TEXTJOIN(",",,IF(TRANSPOSE(ROW(Table1[Participant name]))>ROW(Table1[Participant name]),Table1[Participant name]&" | v.s.| "&TRANSPOSE(Table1[Participant name]),"")),",",REPT(" ",999)),ROW(A1)*999-998,990))

when you add a TEST member you do not need to change the formula

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

Cell Formulas
Range	Formula
C1:C11	C1	=TRIM(MID(SUBSTITUTE(TEXTJOIN(",",,IF(TRANSPOSE(ROW(Table1[Participant name]))>ROW(Table1[Participant name]),Table1[Participant name]&" | v.s.| "&TRANSPOSE(Table1[Participant name]),"")),",",REPT(" ",999)),ROW(A1)*999-998,990))

 

[shaowu459]

Opps, there is something wrong in my post, i will try to modify it later.

 

[Auranim]

Ohh, that's very nice. Is there any way to place the names in different columns?

 

[Auranim]

I tried it by assigning lookup formulas with the participant numbers

 

[shaowu459]

Ohh, that's very nice. Is there any way to place the names in different columns?

could you upload a picture to show your expected result?

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

Cell Formulas
Range	Formula
C1:C11	C1	=TRIM(MID(SUBSTITUTE(TEXTJOIN(",",,IF(TRANSPOSE(ROW(Table1[Participant name]))>ROW(Table1[Participant name]),Table1[Participant name]&" | v.s.| "&TRANSPOSE(Table1[Participant name]),"")),",",REPT(" ",999)),ROW(A1)*999-998,990))

 

[shaowu459]

1) select A1:A6--->Insert--->Table, click OK.
2) Enter formula in C1 and copy formula down to create a helper column.
3) copy formula in column D,E,F and copy down.
4) when you add a participant in the TABLE, all the formula will run automaticaly.

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

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

Add "Test" in table:

Book1.xlsx
	A	B	C	D	E	F
1	Participant name		helper	Participant name1		Participant name2
2	John		2	Michael	| v.s.|	Michael
3	Michael		2	Michael	| v.s.|	Erik
4	Erik		2	Michael	| v.s.|	Dennis
5	Dennis		2	Michael	| v.s.|	Test
6	Test		3	Erik	| v.s.|	Erik
7			3	Erik	| v.s.|	Dennis
8			3	Erik	| v.s.|	Test
9			4	Dennis	| v.s.|	Dennis
10			4	Dennis	| v.s.|	Test
11			5	Test	| 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),"")
E2:E11	E2	=IF(D2<>"","| v.s.|","")
F2:F11	F2	=IFERROR(INDEX(Table1[Participant name],C2+COUNTIF(C$1:C1,C2)),"")

 

[Auranim]

1) select A1:A6--->Insert--->Table, click OK.
2) Enter formula in C1 and copy formula down to create a helper column.
3) copy formula in column D,E,F and copy down.
4) when you add a participant in the TABLE, all the formula will run automaticaly.

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

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

Add "Test" in table:

Book1.xlsx
	A	B	C	D	E	F
1	Participant name		helper	Participant name1		Participant name2
2	John		2	Michael	| v.s.|	Michael
3	Michael		2	Michael	| v.s.|	Erik
4	Erik		2	Michael	| v.s.|	Dennis
5	Dennis		2	Michael	| v.s.|	Test
6	Test		3	Erik	| v.s.|	Erik
7			3	Erik	| v.s.|	Dennis
8			3	Erik	| v.s.|	Test
9			4	Dennis	| v.s.|	Dennis
10			4	Dennis	| v.s.|	Test
11			5	Test	| 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),"")
E2:E11	E2	=IF(D2<>"","| v.s.|","")
F2:F11	F2	=IFERROR(INDEX(Table1[Participant name],C2+COUNTIF(C$1:C1,C2)),"")

Wow. Ok, this is *almost* perfectly what I need. Now the only thing I need it to do is ignore matches in which a participant has to fight himself, since that doesn't make any sense. Is there any way to do that?

 

[shaowu459]

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)),"")

 

[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)),"")

YES! It works! Thank you so much! <3