Help with a fighting simulation

A

Auranim

New Member
Joined
May 16, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
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
ABC
1Participant nameJohn | v.s.| Michael
2JohnJohn | v.s.| Erik
3MichaelJohn | v.s.| Dennis
4ErikMichael | v.s.| Erik
5DennisMichael | v.s.| Dennis
6Erik | v.s.| Dennis
7 
8 
9 
10 
11 
Sheet5
Cell Formulas
RangeFormula
C1:C11C1=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
ABC
1Participant nameJohn | v.s.| Michael
2JohnJohn | v.s.| Michael
3MichaelJohn | v.s.| Erik
4ErikJohn | v.s.| Test
5DennisMichael | v.s.| Erik
6TestMichael | v.s.| Dennis
7Michael | v.s.| Test
8Erik | v.s.| Dennis
9Erik | v.s.| Test
10Dennis | v.s.| Test
11 
Sheet5
Cell Formulas
RangeFormula
C1:C11C1=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))
 
Upvote 0
Opps, there is something wrong in my post, i will try to modify it later.
 
Upvote 0
Ohh, that's very nice. Is there any way to place the names in different columns?
 
Upvote 0
I tried it by assigning lookup formulas with the participant numbers
 
Upvote 0
Auranim said:
Ohh, that's very nice. Is there any way to place the names in different columns?
Click to expand...

could you upload a picture to show your expected result?
Book1.xlsx
ABC
1Participant nameJohn | v.s.| Michael
2JohnJohn | v.s.| Erik
3MichaelJohn | v.s.| Dennis
4ErikJohn | v.s.| Test
5DennisMichael | v.s.| Erik
6TestMichael | v.s.| Dennis
7Michael | v.s.| Test
8Erik | v.s.| Dennis
9Erik | v.s.| Test
10Dennis | v.s.| Test
11 
Sheet5
Cell Formulas
RangeFormula
C1:C11C1=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))
 
Upvote 0
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
ABCDEF
1Participant namehelperParticipant name1Participant name2
2John2Michael| v.s.|Michael
3Michael2Michael| v.s.|Erik
4Erik2Michael| v.s.|Dennis
5Dennis3Erik| v.s.|Erik
63Erik| v.s.|Dennis
74Dennis| v.s.|Dennis
Sheet5
Cell Formulas
RangeFormula
C2:C7C2=IFERROR(SMALL(IF(TRANSPOSE(ROW(Table1[Participant name]))>ROW(Table1[Participant name]),ROW(Table1[Participant name]),""),ROW(A1)),"")
D2:D7D2=IFERROR(INDEX(Table1[Participant name],C2),"")
E2:E7E2=IF(D2<>"","| v.s.|","")
F2:F7F2=IFERROR(INDEX(Table1[Participant name],C2+COUNTIF(C$1:C1,C2)),"")


Add "Test" in table:

Book1.xlsx
ABCDEF
1Participant namehelperParticipant name1Participant name2
2John2Michael| v.s.|Michael
3Michael2Michael| v.s.|Erik
4Erik2Michael| v.s.|Dennis
5Dennis2Michael| v.s.|Test
6Test3Erik| v.s.|Erik
73Erik| v.s.|Dennis
83Erik| v.s.|Test
94Dennis| v.s.|Dennis
104Dennis| v.s.|Test
115Test| v.s.|Test
Sheet5
Cell Formulas
RangeFormula
C2:C11C2=IFERROR(SMALL(IF(TRANSPOSE(ROW(Table1[Participant name]))>ROW(Table1[Participant name]),ROW(Table1[Participant name]),""),ROW(A1)),"")
D2:D11D2=IFERROR(INDEX(Table1[Participant name],C2),"")
E2:E11E2=IF(D2<>"","| v.s.|","")
F2:F11F2=IFERROR(INDEX(Table1[Participant name],C2+COUNTIF(C$1:C1,C2)),"")
 
Upvote 0
shaowu459 said:
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
ABCDEF
1Participant namehelperParticipant name1Participant name2
2John2Michael| v.s.|Michael
3Michael2Michael| v.s.|Erik
4Erik2Michael| v.s.|Dennis
5Dennis3Erik| v.s.|Erik
63Erik| v.s.|Dennis
74Dennis| v.s.|Dennis
Sheet5
Cell Formulas
RangeFormula
C2:C7C2=IFERROR(SMALL(IF(TRANSPOSE(ROW(Table1[Participant name]))>ROW(Table1[Participant name]),ROW(Table1[Participant name]),""),ROW(A1)),"")
D2:D7D2=IFERROR(INDEX(Table1[Participant name],C2),"")
E2:E7E2=IF(D2<>"","| v.s.|","")
F2:F7F2=IFERROR(INDEX(Table1[Participant name],C2+COUNTIF(C$1:C1,C2)),"")


Add "Test" in table:

Book1.xlsx
ABCDEF
1Participant namehelperParticipant name1Participant name2
2John2Michael| v.s.|Michael
3Michael2Michael| v.s.|Erik
4Erik2Michael| v.s.|Dennis
5Dennis2Michael| v.s.|Test
6Test3Erik| v.s.|Erik
73Erik| v.s.|Dennis
83Erik| v.s.|Test
94Dennis| v.s.|Dennis
104Dennis| v.s.|Test
115Test| v.s.|Test
Sheet5
Cell Formulas
RangeFormula
C2:C11C2=IFERROR(SMALL(IF(TRANSPOSE(ROW(Table1[Participant name]))>ROW(Table1[Participant name]),ROW(Table1[Participant name]),""),ROW(A1)),"")
D2:D11D2=IFERROR(INDEX(Table1[Participant name],C2),"")
E2:E11E2=IF(D2<>"","| v.s.|","")
F2:F11F2=IFERROR(INDEX(Table1[Participant name],C2+COUNTIF(C$1:C1,C2)),"")
Click to expand...

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?
 
Upvote 0
Sorry, it is my mistake...i forgot -1 for the formula in D2. please test below formulas:
Book1.xlsx
ABCDEF
1Participant namehelperParticipant name1Participant name2
2John2John| v.s.|Michael
3Michael2John| v.s.|Erik
4Erik2John| v.s.|Dennis
5Dennis2John| v.s.|Test
6Test3Michael| v.s.|Erik
73Michael| v.s.|Dennis
83Michael| v.s.|Test
94Erik| v.s.|Dennis
104Erik| v.s.|Test
115Dennis| v.s.|Test
Sheet5
Cell Formulas
RangeFormula
C2:C11C2=IFERROR(SMALL(IF(TRANSPOSE(ROW(Table1[Participant name]))>ROW(Table1[Participant name]),ROW(Table1[Participant name]),""),ROW(A1)),"")
D2:D11D2=IFERROR(INDEX(Table1[Participant name],C2-1),"")
E2:E11E2=IF(D2<>"","| v.s.|","")
F2:F11F2=IFERROR(INDEX(Table1[Participant name],C2+COUNTIF(C$1:C1,C2)),"")
 
Upvote 0
shaowu459 said:
Sorry, it is my mistake...i forgot -1 for the formula in D2. please test below formulas:
Book1.xlsx
ABCDEF
1Participant namehelperParticipant name1Participant name2
2John2John| v.s.|Michael
3Michael2John| v.s.|Erik
4Erik2John| v.s.|Dennis
5Dennis2John| v.s.|Test
6Test3Michael| v.s.|Erik
73Michael| v.s.|Dennis
83Michael| v.s.|Test
94Erik| v.s.|Dennis
104Erik| v.s.|Test
115Dennis| v.s.|Test
Sheet5
Cell Formulas
RangeFormula
C2:C11C2=IFERROR(SMALL(IF(TRANSPOSE(ROW(Table1[Participant name]))>ROW(Table1[Participant name]),ROW(Table1[Participant name]),""),ROW(A1)),"")
D2:D11D2=IFERROR(INDEX(Table1[Participant name],C2-1),"")
E2:E11E2=IF(D2<>"","| v.s.|","")
F2:F11F2=IFERROR(INDEX(Table1[Participant name],C2+COUNTIF(C$1:C1,C2)),"")
Click to expand...

YES! It works! Thank you so much! <3
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
VBA - Split Source file-Find oldest date in column. Copy all rows with date to new sheet, Repeat on next oldest date. Also, remove some unwanted data.
Replies
0
Views
914
DK643
D
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
395
AndyEd
A
A
  • Solved
Calendar issue
Replies
1
Views
1K
AndyEd
A
C
  • Question
Complex VBA code - Multiple IF conditions including Worksheet Name contains text partially matching Cell
Replies
1
Views
3K
celebwen_orn
C
B
  • Question
Perfect brackets for online tournament [VBA] [Very advanced]
Replies
6
Views
2K
johnnyL
J

Forum statistics

Threads
1,214,853
Messages
6,121,935
Members
449,056
Latest member
denissimo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top