+ 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
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,641

    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!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003
    Posts
    73

    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
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,641

    Re: Help pairing participants in a fighting simulation

    @Croweater

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

  5. #5
    Registered User
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003
    Posts
    73

    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
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,641

    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
    Registered User
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003
    Posts
    73

    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
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,641

    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
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,641

    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