Comparing Excel Arrays for a Golf Draw over 5 Days

P

Peterg4352

New Member
Joined
Mar 18, 2015
Messages
3
20 people go away on a golf trip for 5 days.
Each day has a different "draw" (4 players per group).
The test is that all groups within the trip, must be unique.
I use a random number generator to "randomise" the new groups.

I have tried using this array formulae to test the arrays
={SUM(IF(E10:H10=$E$18:$H$18,1,0))}
The test answer should be greater that 1 if the players on Tuesday have been drawn in the same group on Monday i.e. two players in Monday group 3 are drawn again on Tuesday group 2 but the test indicates that they are not duplicates.

I need some help with the test for duplicates?

[TABLE="width: 906"]
<tbody>[TR]
[TD]Monday[/TD]
[TD]Player 1[/TD]
[TD]Player 2[/TD]
[TD]Player 3[/TD]
[TD]Player 4[/TD]
[TD="align: left"]

<tbody>
</tbody>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group 1[/TD]
[TD]Mr B[/TD]
[TD]Mr L[/TD]
[TD]Mr K[/TD]
[TD]Mr R[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD]Mr Q[/TD]
[TD]Mr E[/TD]
[TD]Mr F[/TD]
[TD]Mr J[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group 3[/TD]
[TD]Mr D[/TD]
[TD]Mr M[/TD]
[TD]Mr H[/TD]
[TD]Mr G[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group 4[/TD]
[TD]Mr O[/TD]
[TD]Mr S[/TD]
[TD]Mr I[/TD]
[TD]Mr C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group 5[/TD]
[TD]Mr A[/TD]
[TD]Mr N[/TD]
[TD]Mr P[/TD]
[TD]Mr T[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]Test Monday Groups[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]Player 1[/TD]
[TD]Player 2[/TD]
[TD]Player 3[/TD]
[TD]Player 4[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Group 1[/TD]
[TD]Mr B[/TD]
[TD]Mr Q[/TD]
[TD]Mr O[/TD]
[TD]Mr N[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD]Mr H[/TD]
[TD]Mr P[/TD]
[TD]Mr K[/TD]
[TD]Mr D[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Group 3[/TD]
[TD]Mr E[/TD]
[TD]Mr J[/TD]
[TD]Mr A[/TD]
[TD]Mr M[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Group 4[/TD]
[TD]Mr T[/TD]
[TD]Mr I[/TD]
[TD]Mr S[/TD]
[TD]Mr L[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Group 5[/TD]
[TD]Mr C[/TD]
[TD]Mr R[/TD]
[TD]Mr F[/TD]
[TD]Mr G[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col span="4"><col span="7"></colgroup>[/TABLE]
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
Maybe:

Excel 2010
DEFGHIJKLMNO

<tbody>
[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"]Monday[/TD]
[TD="bgcolor: #FAFAFA"]Player 1[/TD]
[TD="bgcolor: #FAFAFA"]Player 2[/TD]
[TD="bgcolor: #FAFAFA"]Player 3[/TD]
[TD="bgcolor: #FAFAFA"]Player 4[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA"]Group 1[/TD]
[TD="bgcolor: #FAFAFA"]Mr B[/TD]
[TD="bgcolor: #FAFAFA"]Mr L[/TD]
[TD="bgcolor: #FAFAFA"]Mr K[/TD]
[TD="bgcolor: #FAFAFA"]Mr R[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA"]Group 2[/TD]
[TD="bgcolor: #FAFAFA"]Mr Q[/TD]
[TD="bgcolor: #FAFAFA"]Mr E[/TD]
[TD="bgcolor: #FAFAFA"]Mr F[/TD]
[TD="bgcolor: #FAFAFA"]Mr J[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA"]Group 3[/TD]
[TD="bgcolor: #FAFAFA"]Mr D[/TD]
[TD="bgcolor: #FAFAFA"]Mr M[/TD]
[TD="bgcolor: #FAFAFA"]Mr H[/TD]
[TD="bgcolor: #FAFAFA"]Mr G[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA"]Group 4[/TD]
[TD="bgcolor: #FAFAFA"]Mr O[/TD]
[TD="bgcolor: #FAFAFA"]Mr S[/TD]
[TD="bgcolor: #FAFAFA"]Mr I[/TD]
[TD="bgcolor: #FAFAFA"]Mr C[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FAFAFA"]Group 5[/TD]
[TD="bgcolor: #FAFAFA"]Mr A[/TD]
[TD="bgcolor: #FAFAFA"]Mr N[/TD]
[TD="bgcolor: #FAFAFA"]Mr P[/TD]
[TD="bgcolor: #FAFAFA"]Mr T[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Test Monday Groups[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FAFAFA"]Tuesday[/TD]
[TD="bgcolor: #FAFAFA"]Player 1[/TD]
[TD="bgcolor: #FAFAFA"]Player 2[/TD]
[TD="bgcolor: #FAFAFA"]Player 3[/TD]
[TD="bgcolor: #FAFAFA"]Player 4[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #FAFAFA"]Group 1[/TD]
[TD="bgcolor: #FAFAFA"]Mr B[/TD]
[TD="bgcolor: #FAFAFA"]Mr Q[/TD]
[TD="bgcolor: #FAFAFA"]Mr O[/TD]
[TD="bgcolor: #FAFAFA"]Mr N[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: #FAFAFA"]Group 2[/TD]
[TD="bgcolor: #FAFAFA"]Mr H[/TD]
[TD="bgcolor: #FAFAFA"]Mr P[/TD]
[TD="bgcolor: #FAFAFA"]Mr K[/TD]
[TD="bgcolor: #FAFAFA"]Mr D[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: #FAFAFA"]Group 3[/TD]
[TD="bgcolor: #FAFAFA"]Mr E[/TD]
[TD="bgcolor: #FAFAFA"]Mr J[/TD]
[TD="bgcolor: #FAFAFA"]Mr A[/TD]
[TD="bgcolor: #FAFAFA"]Mr M[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: #FAFAFA"]Group 4[/TD]
[TD="bgcolor: #FAFAFA"]Mr T[/TD]
[TD="bgcolor: #FAFAFA"]Mr I[/TD]
[TD="bgcolor: #FAFAFA"]Mr S[/TD]
[TD="bgcolor: #FAFAFA"]Mr L[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: #FAFAFA"]Group 5[/TD]
[TD="bgcolor: #FAFAFA"]Mr C[/TD]
[TD="bgcolor: #FAFAFA"]Mr R[/TD]
[TD="bgcolor: #FAFAFA"]Mr F[/TD]
[TD="bgcolor: #FAFAFA"]Mr G[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]J17[/TH]
[TD="align: left"]{=SUM(--ISNUMBER(MATCH($E17:$H17,OFFSET($E$8:$H$8,COLUMNS($J17:J17)-1,0),0)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Put the formula in J17, then you can copy it down and across as needed. For the other days, you'll need to adapt the ranges and column references. Let me know how that works.

Depending on how you generate your foursomes, it might be easier to create a VBA macro to create them. Eliminating duplicates could be part of the macro. This could possibly save you some time.
 
Last edited:
Upvote 0
Hi Eric
Many thanks for your advice and my apologies for the delay in replying.
Public Holiday in Australia on Tuesday followed by a very busy catch up day.

Using your code I was not able to replicate your "test" results
but
When I modified the code to delete the OFFSET command as follows:
=SUM(--ISNUMBER(MATCH($E19:$H19,$E$13:$H$13,0)))
SUCCESS
At the end of the day I was only comparing two arrays
The use of the --ISNUMBER and converting the formula to an Array Formula (CSE) made all the difference.

Now that my test is working your comment that the random name generation for the foursome draw is time-consuming is perfectly correct.
It will take forever to randomly generate groups that will satisfy the test.

Can you point me towards some VBA code that will manage the draw?
I am not a VBA expert but can "bumble" my way through the code if given a head start!!

Once again many thanks for your advice.
Very much appreciated.
Peter G
 
Upvote 0
I'm glad you got the test working. I'll try to write up some code as soon as I can. I'll let you know.
 
Upvote 0
I was mentally designing the program. I was a little worried. There are billions of possible combinations, but there are trillions of combinations that don't work. Even with tests at every point, the last foursome of the day might not work, and I'd have to start over.

Then inspiration struck. I manually created a single pattern that works for the entire week. Then I randomly sorted the name list and inserted the names into the pattern. Voila! It instantly creates a list of 5 foursomes over 5 days, and no one ever plays with the same person twice. You can run it as often as you like. You'll probably see a pattern in the list, but if you use your formula to check them, they should all be OK.

To use:

1) Open an empty workbook
2) Put the list of names in A1:A20
3) Right click on the sheet tab on the bottom of the window and select "View Code"
4) Paste the following code into the window that opens
Code: 
Sub Scramble()
Dim Fours(5, 4), MyDays, MyMod, Names1, Names2
Dim x As Integer, r As Integer, c As Integer, MyRow As Integer

' Define a few constants
    MyDays = Array("", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday")
    MyMod = Array(0, 0, 16, 12, 8)
    
' Set up Monday's foursomes
    x = 1
    For r = 1 To 5
        For c = 1 To 4
            Fours(r, c) = x
            Cells(x, "B") = Rnd()
            x = x + 1
        Next c
    Next r
    
' Save the original order of names
    Names1 = Range("A1:A20")
    
' Sort the names randomly
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("B1:B20")
        .SetRange Range("A1:B20")
        .Orientation = xlTopToBottom
        .Apply
    End With
    
' Save the sorted order
    Names2 = Range("A1:A20")
    
' Restore the original list
    Range("A1:A20") = Names1
    Range("B1:B20").ClearContents
    
' Starting row
    MyRow = 2
    
' Do 5 times, once for each day
    For x = 1 To 5
    
' Create the foursomes, using "Fours" as an index into Names2
        Cells(MyRow, "F") = MyDays(x)
        For r = 1 To 5
            Cells(MyRow + r, "F") = "Group" & r
            For c = 1 To 4
                Cells(MyRow + r, c + 6) = Names2(Fours(r, c), 1)
            Next c
        Next r
        
' Now rearrange the indices
        For r = 1 To 5
            For c = 2 To 4
                Fours(r, c) = (Fours(r, c) + MyMod(c)) Mod 20
                If Fours(r, c) = 0 Then Fours(r, c) = 20
            Next c
        Next r
        
        MyRow = MyRow + 8
    Next x
    
End Sub
5) Return to the Excel window
6) Press Alt-F8 to open the macro selection box
7) Select Scramble and click Run

That should do it. Let me know how it works.
 
Upvote 0
He Eric

Love your inspiration ..... the pattern is so simple it is scary!!
I have inserted it into my spreadsheet and as you suspected the previous test showed it was a perfect random draw with no "repeat" players over the five days.
Well done

Many Thanks
Now all I have to do is worry about my golf game!!

Peter G
 
Upvote 0
Well, I can't help you with your golf game, I usually wear a mask when I play so no one will recognize me!

I'm glad the routine works for you - have fun on your trip!
 
Upvote 0
Hi Eric, Nice job! I'm interested in this macro too. Could it also be applied to groups of four with 48 players and 10 rounds instead of 5 days?

Cheers,

Kaz
 
Upvote 0
Hi Kaz,

Thanks for the interest. I'm sure that it could be applied, but I haven't quite figured it out yet. As you may have noticed, the original solution required that I find a pattern manually before I could program it. That pattern does not work for 12 foursomes instead of just 5. I thought I came up with a different pattern that would work, but no such luck. Here's what I have:
Code: 
Sub Scramble()
Dim Fours(12, 4), MyDays, MyMod, Names1, Names2
Dim OldFours
Dim x As Integer, r As Integer, c As Integer, MyRow As Integer

' Define a few constants
    MyDays = Array("", "Round 1", "Round 2", "Round 3", "Round 4", "Round 5", _
                       "Round 6", "Round 7", "Round 8", "Round 9", "Round 10")
    MyMod = Array(0, 0, 1, 5, 7)
    
' Set up Monday's foursomes
    x = 1
    For r = 1 To 12
        For c = 1 To 4
            Fours(r, c) = x
            Cells(x, "B") = Rnd()
            x = x + 1
        Next c
    Next r
    
' Save the original order of names
    Names1 = Range("A1:A48")
    
' Sort the names randomly
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("B1:B48")
        .SetRange Range("A1:B48")
        .Orientation = xlTopToBottom
        .Apply
    End With
    
' Save the sorted order
    Names2 = Range("A1:A48")
    
' Restore the original list
    Range("A1:A48") = Names1
    Range("B1:B48").ClearContents
    
' Starting row
    MyRow = 2
    
' Do 10 times, once for each round
    For x = 1 To 10
    
' Create the foursomes, using "Fours" as an index into Names2
        Cells(MyRow, "F") = MyDays(x)
        For r = 1 To 12
            Cells(MyRow + r, "F") = "Group" & r
            For c = 1 To 4
                Cells(MyRow + r, c + 6) = Names2(Fours(r, c), 1)
            Next c
        Next r
        
' Now rearrange the indices
        OldFours = Fours
        
        For r = 1 To 12
            For c = 2 To 4
                Fours(r, c) = OldFours((r - 1 + MyMod(c)) Mod 12 + 1, c)
            Next c
        Next r
        
        MyRow = MyRow + 15
    Next x
    
End Sub
It's close, but some players do play with each other more than once. I suspect an acceptable pattern might not be quite so easy to find. I'll poke away at it a bit more and let you know what I come up with.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Random Selection of Data
Replies
4
Views
246
Fluff
Fluff
GMC The Macro Man
  • Question Question
Copy n Paste Filtered Data
Replies
1
Views
166
GMC The Macro Man
GMC The Macro Man
B
  • Question Question
Simulator for one event in blackjack game
Replies
2
Views
410
baha17
B
J
  • Question Question
Group last name based on range in alphabet
Replies
2
Views
159
Peter_SSs
Peter_SSs
T
  • Question Question
Convert Libre Calc to Excel - AGGREGATE HLOOKUP Not Working
Replies
1
Views
230
StephenCrump
S

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

Share this page

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
Back
Top