Excel gurus please help!

[iamme]

Would one of the excel gurus on this board please help me?  I am trying to create a 14 week schedule for 40 people at 10 tables and so each player gets to play different people each week (4 people play at once).  Any help would be appreciated.  Thank you so much.

[haapai]

Is this bridge?  Are folks sticking with their regular partners? 

[iamme]

It is for a cribbage league.  We play 4 at a table as partners but we change partners every week so we would like to not play with or against the same people if possible.  Thanks.

[haapai]

I think that the next question regards how reliable participation is.  If all 40 people show up (or send subs) for every bit of cards, coming up with something that mixes them up nicely is quite easy. You can churn out something pretty solid and make a few adjustments for the no-shows.  However, if folks can't be expected to show up or send subs, you'll want to design things differently. 

If you don't know who is going to be there before they actually show up, you may want to resort to something that leans heavily on a random number generator that you pump out after folks have confirmed participation.  What results might not rotate folks as thoroughly as you hope, but it won't create a dual system where some folks get a nice exposure to everyone who shows up regularly and others get sidetracked into some sort of substitute and truant infested subsystem that has them playing the same unenthusiastic and unreliable players week after week. 

[iamme]

We will have 40 people each week reliably and we are trying to not play the same people more than one time if that is possible. Thanks.

[Deleted]

Its impossible never to have any repeats.


Assuming person 1 is in every round for 14 weeks, she would need to play 3 new people per week, meaning 42 individuals, so even at the most basic level without delving into the math further, its impossible to have no repeats. 

If I understood you intent properly?

[iamme]

OK as few repeats as possible- thanks

[Virgil Showlion]

To assist you, I created a "fiddle" (an online script) that should solve your problem.

Go to jsfiddle.net/t0d0wfqy/1/

In the lower right panel, you'll see a place to input player names. Put in all 40 player names, one per line. For example:

Marcy P.
Jim K.
Sarah Johnson
...

Click on "Calculate".

The groupings for all 10 tables for 4 weeks will appear in the field below the button. You can then input this data into a spreadsheet.

The script generates groupings such that: i) all 40 people play exactly once each week, and ii) the number of repeated pairings is minimized.

[Deleted]

That does lots of repeats though Virgil. I just assigned letters and only checked the first row it repeated entire sequence once and Q repeated o, O, K, c, T...


I don’t know if excel does that kind of formula.

But start 1 -10
               11-20
               21-30
               31-40

Keep first Line, rotate second one position, third two positions, last 3 positions. (Or maybe 3 and 5?) 

so next set would be. 1 -10
                                   12-11
                                   23-22
                                   34-33

etc. I think would give the most variation. Then go back and group 1-4, 5-8, etc.

 

[Jaguar]

I just done 40 different names over 14 weeks and I got no repeats, or I think no repeats. This was with Virgils link.

[Virgil Showlion]

Oct 23, 2017 22:33:05 GMT -5 oped said:

That does lots of repeats though Virgil. I just assigned letters and only checked the first row it repeated entire sequence once and Q repeated o, O, K, c, T...


I don’t know if excel does that kind of formula.

But start 1 -10
               11-20
               21-30
               31-40

Keep first Line, rotate second one position, third two positions, last 3 positions. (Or maybe 3 and 5?) 

so next set would be. 1 -10
                                   12-11
                                   23-22
                                   34-33

etc. I think would give the most variation. Then go back and group 1-4, 5-8, etc.

Putting in a-z and A-N (40 characters total), I get:

Week 1:

Table 1: a, b, c, d
Table 2: e, f, g, h
Table 3: i, j, k, l
Table 4: m, n, o, p
Table 5: q, r, s, t
Table 6: u, v, w, x
Table 7: y, z, A, B
Table 8: C, D, E, F
Table 9: G, H, I, J
Table 10: K, L, M, N

Week 2:

Table 1: a, e, i, m
Table 2: q, u, y, C
Table 3: G, K, b, f
Table 4: j, n, r, v
Table 5: z, D, H, L
Table 6: c, g, k, o
Table 7: s, w, A, E
Table 8: I, M, d, h
Table 9: l, p, t, x
Table 10: B, F, J, N

Week 3:

Table 1: a, h, o, v
Table 2: C, J, c, j
Table 3: q, x, E, L
Table 4: e, l, s, z
Table 5: G, N, g, n
Table 6: u, B, I, b
Table 7: i, p, w, D
Table 8: K, d, k, r
Table 9: y, F, M, f
Table 10: m, t, A, H

Week 4:

Table 1: a, k, u, E
Table 2: b, l, v, F
Table 3: c, m, w, G
Table 4: d, n, x, H
Table 5: e, o, y, I
Table 6: f, p, z, J
Table 7: g, q, A, K
Table 8: h, r, B, L
Table 9: i, s, C, M
Table 10: j, t, D, N

There are repeated pairings, but as you pointed out earlier, it's impossible to avoid duplicates entirely.

[iamme]

Many thanks!

Exactly what I needed!