List all combinations of players for matches

[plsm5882]

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

 

[svendiamond]

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]

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.

 

[plsm5882]

bump

 

[svendiamond]

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]

Alright I set my sheet up like this:

Book1
	C	D	E
4	Players
5	John
6	Mike
7	Tom
8	Bill
9
10
11
12
13
14
15		Player	Vs
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Sheet1

And executed this 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):

Book1
	C	D	E
4	Players
5	John
6	Mike
7	Tom
8	Bill
9
10
11
12
13
14
15		Player	Vs
16		John	Mike
17		John	Tom
18		John	Bill
19		Mike	Tom
20		Mike	Bill
21		Tom	Bill
22
23
24
25
26
27
28
29
30
Sheet1

 

[svendiamond]

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]

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]

Actually, one other thing... Rather than list the matches:

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Player 1[/TD]
[TD="width: 64"]Player 2[/TD]
[/TR]
[TR]
[TD]Player 1[/TD]
[TD]Player 3[/TD]
[/TR]
[TR]
[TD]Player 1[/TD]
[TD]Player 4[/TD]
[/TR]
[TR]
[TD]Player 2[/TD]
[TD]Player 3[/TD]
[/TR]
[TR]
[TD]Player 2[/TD]
[TD]Player 4[/TD]
[/TR]
[TR]
[TD]Player 3[/TD]
[TD]Player 4
[/TD]
[/TR]
</tbody>[/TABLE]

It would be better if the matches were listed:

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Player 1[/TD]
[TD="width: 64"]Player 2[/TD]
[/TR]
[TR]
[TD]Player 3[/TD]
[TD]Player 4[/TD]
[/TR]
[TR]
[TD]Player 1[/TD]
[TD]Player 3[/TD]
[/TR]
[TR]
[TD]Player 2[/TD]
[TD]Player 4[/TD]
[/TR]
[TR]
[TD]Player 1[/TD]
[TD]Player 4[/TD]
[/TR]
[TR]
[TD]Player 2[/TD]
[TD]Player 3[/TD]
[/TR]
</tbody>[/TABLE]

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

Thx

 

[svendiamond]

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?