Dominoes team selection randomly

[Rmejia]

I'm new on this and need help with a macro I need to create. The goal is to randomly select partners for dominoes matches from a list of names.
Details:
I will have a list of players in column "B", every day could be a different amount of players and different names. In column "A" those players will have a unique number. The macro should combine those players in pairs and those partners will go against another pair both pairs randomly selected. I will specify on a cell how many times each player will play on that day. The constraints are that:

1. No two players should play together on a team for the second time until all combinations have been used, and so on.
2. No two same teams should play against each other for the second time until all combinations have been used.

What I need back from the macro is in …

• Column D: Team 1, (Player 1 number).
• Column E: Team 1, (Player 2 number).
• Column F: Team 2, (Player 1 number).
• Column G: Team 2, (Player 2 number).

I only need the number since my plan is to get the name from the master list with a <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; background-color: rgb(239, 240, 241); white-space: pre-wrap;">vlookup</code>. Each row will be a different match. If for example, I have 8 players and each will play 5 times, at the end, I should have 10 rows with the matches:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">8 players x 5 games per player =40/4 players each match =10
</code>Please let me know if you have any questions, Thanks.

 

[cerfani]

try this code... ... it was able to take the list of values in A and select from them randomly in groups of 2 vs 2 and displays in D-G

Sub SelectRandomPlayers()
    Dim players() As String, selectedPlayers(3) As String
    Dim playerRange As Range
    Dim i As Long, c As Long, writeRow As Long, ub As Long, selectedIndex As Long, j As Long, ub2 As Long
    
    writeRow = 2
    
    Set playerRange = Range(Range("A2"), Range("A2").End(xlDown))
    c = playerRange.count
    ub = c - 1
    ReDim players(ub)
    
    For i = 0 To ub
        players(i) = playerRange.Cells(i + 1).Value2
    Next i
    
    Do
        For i = 0 To 3
            ub2 = UBound(players)
            selectedIndex = WorksheetFunction.RandBetween(0, ub2)
            selectedPlayers(i) = players(selectedIndex)
            
            For j = selectedIndex To ub2
                If j < ub2 Then players(j) = players(j + 1)
            Next j
            
            If ub2 > 0 Then ReDim Preserve players(ub2 - 1)
        Next i
        
        Range("D" & writeRow).Value2 = selectedPlayers(0)
        Range("E" & writeRow).Value2 = selectedPlayers(1)
        Range("F" & writeRow).Value2 = selectedPlayers(2)
        Range("G" & writeRow).Value2 = selectedPlayers(3)
        writeRow = writeRow + 1
    Loop While UBound(players) > 0
End Sub

 

[Rmejia]

Excellent, thanks for the prompt response.

Is there a way to do this part:

I will specify on a cell how many times each player will play on that day. The constraints are that:

1. No two players should play together on a team for the second time until all combinations have been used, and so on.
2. No two same teams should play against each other for the second time until all combinations have been used.

I noticed that the code does comply with these constraints but it only has 1 match per player. How can I specify how many matches each player should play?

Thanks in advance.

 

[shg]

1. No two players should play together on a team for the second time until all combinations have been used, and so on.

2. No two same teams should play against each other for the second time until all combinations have been used.

I don't believe there is a general solution to this (and certainly not for groups > 2), but there are published solutions:

8​	1​	AB​	CD​	EF​	GH​
	2​	AC​	BD​	EG​	FH​
	3​	AD​	BC​	EH​	FG​
	4​	AE​	BF​	CG​	DH​
	5​	AF​	BE​	CH​	DG​
	6​	AG​	BH​	CE​	DF​
	7​	AH​	BG​	CF​	DE​
10​	1​	AB​	CD​	EF​	GH​	IJ​
	2​	AC​	BD​	EG​	FI​	HJ​
	3​	AD​	BC​	EJ​	FG​	HI​
	4​	AE​	BF​	CH​	DI​	GJ​
	5​	AF​	BE​	CJ​	DH​	GI​
	6​	AG​	BI​	CE​	DJ​	FH​
	7​	AH​	BG​	CI​	DE​	FJ​
	8​	AJ​	BH​	CF​	DG​	EI​
	9​	AI​	BJ​	CG​	DF​	EH​
12​	1​	AB​	CD​	EF​	GH​	IJ​	KL​
	2​	AC​	BD​	EG​	FH​	IK​	JL​
	3​	AD​	BC​	EH​	FG​	IL​	JK​
	4​	AE​	BF​	CI​	DJ​	GK​	HL​
	5​	AF​	BE​	CJ​	DI​	GL​	HK​
	6​	AK​	BL​	CE​	DF​	GI​	HJ​
	7​	AL​	BK​	CF​	DE​	GJ​	HI​
	8​	AG​	BH​	CK​	DL​	EI​	FJ​
	9​	AH​	BG​	CL​	DK​	EJ​	FI​
	10​	AI​	BJ​	CG​	DH​	EK​	FL​
		AJ​	BI​	CH​	DG​	EL​	FK​
14​	1​	AB​	CD​	EF​	GH​	IJ​	KL​	MN​
	2​	AC​	BD​	EG​	FH​	IK​	JM​	LN​
	3​	AD​	BC​	EH​	FG​	IN​	JK​	LM​
	4​	AE​	BF​	CG​	DH​	IL​	JN​	KM​
	5​	AF​	BE​	CH​	DG​	IM​	JL​	KN​
	6​	AI​	BJ​	CE​	DK​	FL​	GM​	HN​
	7​	AJ​	BI​	CK​	DE​	FN​	GL​	HM​
	8​	AK​	BM​	CF​	DI​	EJ​	GN​	HL​
	9​	AL​	BN​	CI​	DF​	EM​	GJ​	HK​
	10​	AG​	BK​	CL​	DN​	EI​	FM​	HJ​
	11​	AM​	BG​	CJ​	DL​	EN​	FK​	HI​
	12​	AH​	BL​	CN​	DM​	EK​	FJ​	GI​
	13​	AN​	BH​	CM​	DJ​	EL​	FI​	GK​
16​	1​	AB​	CD​	EF​	GH​	IJ​	KL​	MN​	OP​
	2​	AC​	BD​	EG​	FH​	IK​	JL​	MO​	NP​
	3​	AD​	BC​	EH​	FG​	IL​	JK​	MP​	NO​
	4​	AE​	BF​	CG​	DH​	IM​	JN​	KO​	LP​
	5​	AF​	BE​	CH​	DG​	IN​	JM​	KP​	LO​
	6​	AG​	BH​	CE​	DF​	IO​	JP​	KM​	LN​
	7​	AH​	BG​	CF​	DE​	IP​	JO​	KN​	LM​
	8​	AI​	BJ​	CK​	DL​	EM​	FN​	GO​	HP​
	9​	AJ​	BI​	CL​	DK​	EN​	FM​	GP​	HO​
	10​	AK​	BL​	CI​	DJ​	EO​	FP​	GM​	HN​
	11​	AL​	BK​	CJ​	DI​	EP​	FO​	GN​	HM​
	12​	AM​	BN​	CO​	DP​	EI​	FJ​	GK​	HL​
	13​	AN​	BM​	CP​	DO​	EJ​	FI​	GL​	HK​
	14​	AO​	BP​	CM​	DN​	EK​	FL​	GI​	HJ​
	15​	AP​	BO​	CN​	DM​	EL​	FK​	GJ​	HI​

[td="bgcolor:#F3F3F3"]

Players​

[/td][td="bgcolor:#F3F3F3"]

Round​

[/td][td="bgcolor:#F3F3F3"]

Game 1​

[/td][td="bgcolor:#F3F3F3"]

Game 2​

[/td][td="bgcolor:#F3F3F3"]

Game 3​

[/td][td="bgcolor:#F3F3F3"]

Game 4​

[/td][td="bgcolor:#F3F3F3"]

Game 5​

[/td][td="bgcolor:#F3F3F3"]

Game 6​

[/td][td="bgcolor:#F3F3F3"]

Game 7​

[/td][td="bgcolor:#F3F3F3"]

Game 8​

[/td]

 

[cerfani]

Oops I must have glanced over that part but it would change the code completely. You are asking for something that I do not think is so straightforward. Like the other poster mentioned, there are solutions to create all unique combinations of items

Look at this code...

Creating a list of all possible unique combinations from an array (using VBA) - Stack Overflow

I wonder how many players you have because like the other post mentioned you can just select the combinations randomly from a list since you want to exhaust all possible combinations. This calculator can actually tell you how many team combinations there are... Combinations Calculator (nCr). n = player count and r = team size. But not only that but you would need to keep track of match history to ensure two teams dont play a 2nd time.

It isn't difficult code to write but it is tedious. Unless someone knows a better way. The code I gave you selects items randomly from an array and removes the item from the array. This acts like a List (in .Net) which would be helpful. Honestly this would be easier to write in .Net than vba lol

 

[Rmejia]

Thanks, is there a way to do at least this:

I will specify on a cell how many times each player will play on that day.

 

[cerfani]

Thanks, is there a way to do at least this:

I will specify on a cell how many times each player will play on that day.

Yeah sure. It is a matter of programming it. You would need to...


1. List your Players in a column. A;B;C;D;E;F;...
2. Create unique teams in another column. AB;AC;AD;AE;AF;BC;BD;...
3. Create list of unique matches in another column. ACBD;...
4. Remove all instances of matches where a player is on both sides, eg, ABBC (cant have team AB play team BC since player B is on both sides)
5. Now randomize the order of the list of matches
6. Track a counter for each player in a column next to the player column and subtract from their counter everytime a player plays a game and prevent matches if their counter is 0. (example, give an error message or something)

This is not your typical spreadsheet, sounds like a matchmaking app.

 

[Rmejia]

hahaha, I don't think my abilities reach that far, can you help me with the programming of that?

 

[cerfani]

Sure, write some code, give it a try, google stuff, give it your best shot and if you get stuck, paste your code here and i will try to look at it when I am not busy

I normally dont mind writing code for people but this is not a quick task. I would have to go through those links and figure it out as I have never had to make code like this. It would take time to debug it all. But I dont mind looking at your code and helping.

 

[AhoyNC]

You may want to look at this website for setting random golf pairings (it might work for your teams).
As both SHG and cerfani have pointed out, this is not an easy task to accomplish in Excel.

Free round robin tournament schedule / pairings generator ? GolfSoftware.com