List all combinations of players for matches

plsm5882

New Member
Joined
Apr 22, 2016
Messages
20
Hi.

I have a list of players in C5:C14. Not all cells in that range will always have a player name in it.

In C16:D110 I'd would like to build the match linups.

For example, on Sheet2, Sheet3, Sheet4 and Sheet5:

C1: Player 1
C2: Player 2
C3: Player 3
C4: Player 4

C16: Player 1 D16: Player 2
C17: Player 3 D17: Player 4
C18: Player 1 D17: Player 3
C19: Player 2 D19: Player 4
C20: Player 1 D20: Player 4
C21: Player 2 D21: Player 3

It should run this code whenever anything changes in column 6 on Sheet6.

Is anyone able to please help me out as I am a complete VBA newbie.

Thanks
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

svendiamond

Well-known Member
Joined
Jun 13, 2014
Messages
1,498
Office Version
  1. 365
Platform
  1. Windows
Soo you just said that you "have a list of players in C5:C14" but then you give this example:

For example, on Sheet2, Sheet3, Sheet4 and Sheet5:

C1: Player 1
C2: Player 2
C3: Player 3
C4: Player 4


So which is it? And what sheets are we working with? Why Sheet2, Sheet3, etc... you didn't discuss any of that. And what is significant about column 6 on Sheet6? What is in that column, and how will it affect the other sheets?
 

plsm5882

New Member
Joined
Apr 22, 2016
Messages
20
Thanks for replying. I was trying to keep it simple and to the point.

Its a tournament spreadsheet. Sheet6 holds players names, handicaps and group numbers. Column 6 has group numbers.

Sheets 2-5 are identical but used for different groups. Those sheets hold match lineups and results.

And, as I said at the start of the post, in that C5:C14 range on each sheet, not all the cells in the range will always have player names. The number of players depends on how many players are in the tournament and in the groups, so how many players are on Sheet6 and column 6.

Anyway, I appreciate any help you can provide.
 

svendiamond

Well-known Member
Joined
Jun 13, 2014
Messages
1,498
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Is there a Home and Away... or just each player playing each other player once?

Also, are there always an even number of players? Or could there be any amount up to 10 (since the range is C5:C14 which is 10 cells)?
 

svendiamond

Well-known Member
Joined
Jun 13, 2014
Messages
1,498
Office Version
  1. 365
Platform
  1. Windows
Alright I set my sheet up like this:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFC000;;">Players</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">John</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">Mike</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">Tom</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">Bill</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;"></td><td style="text-align: right;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;;"></td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Player</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Vs</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />

And executed this code:

Code:
Sub createMatchups()


Dim myPlayer As String, myLoop As Long, nextBlank As Long
Dim playerCount As Long


With Sheet1
    playerCount = Application.WorksheetFunction.CountA(.Range("C5:C14"))
    For myLoop = 5 To (5 + playerCount - 2)
        nextBlank = .Cells(.Rows.Count, 4).End(xlUp).Offset(1).Row
        If .Cells(myLoop, 3).Value <> "" Then
            .Cells(nextBlank, 4).Value = .Cells(myLoop, 3).Value
            
            If nextBlank < nextBlank + playerCount - (myLoop - 3) Then
                .Range(.Cells(nextBlank, 4), .Cells(nextBlank + playerCount - (myLoop - 3), 4)).FillDown
            End If
            
            .Range(.Cells(myLoop + 1, 3), .Cells(myLoop + playerCount - (myLoop - 4), 3)).Copy
            .Cells(nextBlank, 5).PasteSpecial xlPasteValues
            Application.CutCopyMode = False
        End If
    Next myLoop
End With


End Sub

And got this result (it also worked with more names):

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFC000;;">Players</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">John</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">Mike</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">Tom</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">Bill</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;"></td><td style="text-align: right;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;;"></td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Player</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Vs</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;">John</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;">Mike</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;">John</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;">Tom</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;">John</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;">Bill</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;">Mike</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;">Tom</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;">Mike</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;">Bill</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;">Tom</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;">Bill</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BDD7EE;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />
 

svendiamond

Well-known Member
Joined
Jun 13, 2014
Messages
1,498
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Let me know if that is what you want. And if so, I will adjust it to loop through Sheets 1 through 5 anytime Sheet6!F:F is changed.
 

plsm5882

New Member
Joined
Apr 22, 2016
Messages
20
That looks right apart from you have shifted it the match ups into d and e. But I can work with that. Thanks very much.
 

plsm5882

New Member
Joined
Apr 22, 2016
Messages
20
Actually, one other thing... Rather than list the matches:

Player 1Player 2
Player 1Player 3
Player 1Player 4
Player 2Player 3
Player 2Player 4
Player 3Player 4

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

It would be better if the matches were listed:

Player 1Player 2
Player 3Player 4
Player 1Player 3
Player 2Player 4
Player 1Player 4
Player 2Player 3

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

So listed in a realistic match playing order. Dont know if I explained that properly.

Thx
 

svendiamond

Well-known Member
Joined
Jun 13, 2014
Messages
1,498
Office Version
  1. 365
Platform
  1. Windows
Hmm... first I want to ask is there always an even number of players? What the maximum number of potential players and what is the minimum?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,387
Messages
5,624,389
Members
416,026
Latest member
melvic69

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
Top