+ Reply to Thread
Results 1 to 10 of 10

Help pairing participants in a fighting simulation

  1. #1
    Registered User
    Join Date
    05-16-2020
    Location
    Brazil
    MS-Off Ver
    365
    Posts
    2

    Question Help pairing participants in a fighting simulation

    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.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: Help pairing participants in a fighting simulation

    Welcome to the forum.

    With PowerQuery in Excel 365, this is easy. First, load the participants table into PQ, get rid of the leftmost column and convert to a list. Save to connection only. Next, load the participants table a second time and use this M Code:

    Please Login or Register  to view this content.
    Excel 2016 (Windows) 32 bit
    D
    E
    1
    Paricipant Name Opponent Name
    2
    John Michael
    3
    John Erik
    4
    John Dennis
    5
    Michael Erik
    6
    Michael Dennis
    7
    Erik Dennis
    Sheet: Sheet1
    Attached Files Attached Files
    Last edited by AliGW; 05-17-2020 at 01:07 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Spammer
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    1,237

    Re: Help pairing participants in a fighting simulation

    Or what you could do is put the participants in column A (my example starts in row 3), and the first two opponents in manually (say in B3 and C3) and drag the following formulas down from cells B4 and C4 (for the number of matches you have).

    =IF(INDEX($A$3:$A$99,MATCH(C3,$A$3:$A$99,0)+1)=0,INDEX($A$3:$A$99,MATCH(B3,$A$3:$A$99,0)+1),B3)

    =IF(INDEX($A$3:$A$99,MATCH(C3,$A$3:$A$99,0)+1)=0,INDEX($A$3:$A$99,MATCH(B4,$A$3:$A$99,0)+1),INDEX($A$3:$A$99,MATCH(C3,$A$3:$A$99,0)+1))

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: Help pairing participants in a fighting simulation

    @Croweater

    OT, but is your user profile up-to-date?

  5. #5
    Spammer
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    1,237

    Re: Help pairing participants in a fighting simulation

    @AliGW

    Well, everything that is in there is accurate as far as I can tell. Do you suspect otherwise?

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: Help pairing participants in a fighting simulation

    I am always surprised when people are still using Excel 2003, but if that is correct, that's fine.

  7. #7
    Spammer
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    1,237

    Re: Help pairing participants in a fighting simulation

    I'm old school and don't believe in replacing something that does the job just fine for me.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: Help pairing participants in a fighting simulation

    Ah, what a shame - you are missing out on the Pandora's Box that is PowerQuery (see above).

    Anyway, hopefully we'll get some feedback from the OP soon. (S)he has two options now.

  9. #9
    Registered User
    Join Date
    05-16-2020
    Location
    Brazil
    MS-Off Ver
    365
    Posts
    2

    Re: Help pairing participants in a fighting simulation

    Quote Originally Posted by AliGW View Post
    (S)he has two options now.
    And both work wonders :P PowerQuery is neater, but I'll probably go with Index/match because I suspect a few people will try to run this on Google Sheets. Got where I needed to be though, so that's fantastic! <3 thanks for the help guys!

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: Help pairing participants in a fighting simulation

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Elo rating database for up to 500 participants
    By aigoo in forum Excel General
    Replies: 3
    Last Post: 08-13-2018, 07:23 PM
  2. From Fire Fighting to Office/Desk job
    By patwee84 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 07-05-2018, 10:29 PM
  3. Pages Generated by Number of Participants
    By Hungry Horace in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 12-18-2016, 07:16 AM
  4. League for 8 participants
    By Melis K. in forum Excel General
    Replies: 2
    Last Post: 04-11-2013, 05:13 AM
  5. [SOLVED] # of participants in a program during the reporting period
    By LaurieLaurie in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-12-2013, 02:30 PM
  6. Calculate %age of participants with 66% atttendance using two different files
    By ajang in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-05-2012, 05:03 PM
  7. list of shared workbook participants
    By sam1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-20-2005, 01:33 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1