Consulting

Results 1 to 6 of 6

Thread: Fixture List

  1. #1
    VBAX Newbie
    Joined
    May 2023
    Posts
    1
    Location

    Fixture List

    I'm trying to create a small league. The league would have between 6 and 20 teams. I've been trying to create a function that would create fixtures whereby teams would play all others once only in different Rounds. I've created the 1st round with a Do Until loop but after that I'm having problems. Has anybody else done this or have any idea how to create the function?

    1 v 2
    3 v 4
    5 v 6
    7 v 8

    1 v 3
    2 v 5
    4 v 7
    6 v 8

    etc

  2. #2
    google combination/permutation code.

  3. #3
    VBAX Newbie
    Joined
    May 2023
    Posts
    2
    Location
    I am not clear much when using google combination/permutation code.

  4. #4
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,999
    Location
    Okay to create a list all combinations of "fixture" matches, place a list of all teams( as I don't know your teams, I used a to k) in column A. Copy the filled range and paste into Column B. Then in cell C1 enter the following formula
     =IF(ROW()-ROW($C$1(+1>COUNTA(A:A)*COUNTA(B:B)," ",INDEX(A:A,INT((Row()-ROW($C$1))/COUNTA(B:B)+1)) & "," & INDEX(B:B,MOD(ROW()-ROW($C$1),COUNTA(B:B))=1))
    Copy down until no more data is filled. In my test workbook I used 11 teams (a to k), and this filled down to cell C121. Data in Column C will show as a,a a,b a,c etc. Common sense will tell you there are two rules to be applied here, and these are a team cannot play against itself in a Fixture match, and secondly a team cannot play against a team for which it has already played. So we need to find a method/s to remove the incorrect data.

    I'm not confident that this code as supplied is correct, and I'm hoping that someone will provide the correct solution.

    Sub CheckTeams()
    Dim i as Long
    Dim lRow as Long
    With Sheets("Sheet1")
        For  i = lRow to 1 step -1
             If .cells(I,"C").Value  = StrReverse(i,"C").value Then
                .Cells(i).Delete
            End If
        Next I
    End With
    With Sheets("Sheet1")
        For each cell in Range("C1:C & lRow")
            If(Left(C& i,1)=Right(C & i,1) Then
                .Cells(i).Delete
            End If
        Next I
    End With
    End Sub
    What I was hoping here in the first part, was to be able to start with the last value in Column C and compare against the string reversed of every other cell and if found then delete the cell you are looking up. And in the second part I am trying to remove a match where a team plays against itself.

    As I said earlier, untested and I'm not a coder. For the Code guru's, when you've finished laughing, can you please supply the solution?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  5. #5
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    As @arnelgp pointed out earlier, GIYBF for this:
    https://www.get-digital-help.com/rou...in-tournament/
    Attached Files Attached Files
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  6. #6
    VBAX Newbie
    Joined
    May 2023
    Posts
    2
    Location
    Thank you

Posting Permissions

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