Comparing Excel Arrays for a Golf Draw over 5 Days

[Peterg4352]

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]

 

[oriwitt]

There's quite a bit of literature on permutations which would get the Set from which you should randomize.

 

[moze83]

Hi Eric,

Thanks for getting back to me and for having a go! It wouldn't matter if some players played the same person twice. Just wanted everyone to play as many different players as possible. Would love to hear back if you come up with the miracle pattern.

Thanks again for you help,

Kaz

 

[Eric W]

oriwitt - True, there is a lot of literature out there. This is probably the most useful page I've found so far:

Math Games: Social Golfer Problem

Sadly, nothing I've found has a solution for the exact situation requested here. The table near the bottom strongly suggests that there is a solution for 12 4-person groups, 10 days, but does not give that solution. I don't think I've got time enough to actually work through the mathematics to calculate it. I may try to constrain the problem as tightly as possible, and then set up a randomizer to look at combinations. If I can constrain it tight enough, I might get lucky and find one.

The method I came up with uses modulus arithmetic to come up with patterns. I haven't quite determined why it doesn't work, I may go back and dig a bit more there.

If I come up with something, I'll let you both know.

 

[shg]

From Wolfram Demonstrations Project

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][/tr][tr][td]

38​

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

Day 1​

[/td][td]

ABCD​

[/td][td]

EFGH​

[/td][td]

IJKL​

[/td][td]

MNOP​

[/td][td]

QRST​

[/td][/tr]
[tr][td]

39​

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

Day 2​

[/td][td]

AEIM​

[/td][td]

BFJQ​

[/td][td]

CGNR​

[/td][td]

DKOS​

[/td][td]

HLPT​

[/td][/tr]
[tr][td]

40​

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

Day 3​

[/td][td]

AFOT​

[/td][td]

BELR​

[/td][td]

CIPS​

[/td][td]

DGJM​

[/td][td]

HKNQ​

[/td][/tr]
[tr][td]

41​

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

Day 4​

[/td][td]

AJPR​

[/td][td]

BHMS​

[/td][td]

CEKT​

[/td][td]

DFIN​

[/td][td]

GLOQ​

[/td][/tr]
[tr][td]

42​

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

Day 5​

[/td][td]ALNS[/td][td]BGIT[/td][td]CHJO[/td][td]DEPQ[/td][td]

FKMR​

[/td][/tr]
[/table]

 

[oriwitt]

Looking forward to hearing of it!

 

[oriwitt]

Would anything from this thread help?

http://www.mrexcel.com/forum/excel-...c-applications-combinations-permutations.html

Can then just select the 4 element sets out of it up to the number rounds you want

 

[Eric W]

This will probably be my last post on this subject.

shg, thanks for the link, I hadn't found that one yet. As far as the 5 foursomes in 5 days problem, I had already solved that one in post #5. Then in post #9, moze83 asked if I could apply that solution to 12 foursomes over 10 rounds, and I've been looking at that. Unfortunately, the applet you found doesn't go up to 12.

What I have found:

1) This is called the "Social Golfer" problem, and it's been worked on for over 150 years.
2) It has not been "solved" and is considered a difficult problem.
3) Some solutions exist for special cases, but there is no known method of constructing a solution for a given situation.
4) I found one page which describes a method of constructing a solution if the number of golfers is divisible by 4 (48 is!), and the number 1 below that is divisible by 3 (47 isn't!).
Google Answers: Math Question for Golf Teaming
5) Based on the link I put in post #13, I strongly believe that a solution for 12 foursomes over 10 rounds exists, but I don't have a way to find it, and I haven't been lucky enough to stumble on it. And I've tried quite a few ways.

oriwitt, I didn't find anything in that thread that helped. But thanks for looking!

Based on that, I'm going to stop working on this problem. I doubt my desultory efforts will find a solution where hundreds of dedicated mathematicians have failed. moze83, I hope my macro that mixes up the players, even though there are some repeats, is good enough for you. But thanks a lot, I learned a lot, and it was fun! (For a while anyway, then it got less fun quickly.)

 

[moze83]

Hi Eric,

Thanks for all your help and info. I ended up using the 28 players over 9 rounds combined with your 20 players over 5 rounds x 2 and manually filled the gaps. There are always about 4 groups less each round due to players with other commitments. The gaps each week in the 28players 9rounds will be filled by other 20 x 5 players. This should mix the groups up a bit more. Not a perfect result but pretty good and a much better spread than having them drawn out of a hat as previously done..

Thanks again,

Kaz