Reshuffle group combinations without repitition...

Hi all,

I need a help on this one. For me this is way too complicated but I am sure there are enough maestros out here to offer helping hand.

Lets say there are 24 golfers. I want to create six groups of 4 golfers each.

The golfers will play for 4 months. After every week, the golfers get reshuffled so that they don't play with same combination again. So in all there will be sixteen reshufflings in four months.

Is there any faster way to achieve this group of combinations in excel?

Any formula or macro or something? Please help.

Thanks in advance.

That's where the complexity and the limitation's of the formulas come in. there are too many variables/flags that would have to be checked by a formula so that a schedule would go as long as it could before repeating.

The formula/macro/program would have to lets say for player 1.
Would not only have to check who he played with all previous weeks and eliminate those that he played with, it would have to then check the players left to see who they played with and eliminate the players they already played with and so on till your left with only those who have never played together and put them in for that week. plus it would have to check who has already been used for another team.

Example:
Week 1-
Team 1 players are 1-2-3-4
Team 2 players are 5-6-7-8
Team 3 players are 9-10-11-12
and so on.

Week 2-
Team 1 players are 1-5-9-13
Why these numbers? Because:
player 1 cant play with 2-3-4 because they played together already next available number is 5
player 5 cant play with 6-7-8 because they played together already next available number is 9
player 9 cant play with 10-11-12 because they played together already next available number is 13

Team 2 players are 2-6-10-14
here it had to also check team 1 to eliminate 5-9-13 because they are being used by team 1.

So you see there would be a lot of cross referencing numbers that would have to be done.

But then at some point its going to start repeating.

24 players in 6 groups of 4 players playing once a week. can go I think about 7 weeks before somebody has to play with someone they played with before.

Now a couple of people have said you might could do it with a database, but I've never seen one or a example and I don't about making databases.

I've been working on something similar, a bowling schedule generator. It's almost impossible for a spreadsheet, as far as i can tell.

Macros are needed to really get close and its still hard. For golf it might be a little easier, because you don have to worry about moving to different lanes every week.

But here's a place you can start this formula works ok for a small group. you should be able to change it maybe.

Some search terms are Round robin, Fixtures, fixtures list, League tables, team schedules also there are some free sites that will make them.

16 team/players

...cell.A..............................cell......B
...Week.....................................1
=IF(B1+1<16,B1+1,1)...............=IF(B3+1<16,B3+1,1)
=IF(A2+1<16,A2+1,1)...............=IF(B4+1<16,B4+1,1)
=IF(A3+1<16,A3+1,1)...............=IF(B5+1<16,B5+1,1)
=IF(A4+1<16,A4+1,1)...............=IF(B6+1<16,B6+1,1)
=IF(A5+1<16,A5+1,1)...............=IF(B7+1<16,B7+1,1)
=IF(A6+1<16,A6+1,1)...............=IF(B8+1<16,B8+1,1)
=IF(A7+1<16,A7+1,1)...............=IF(A8+1<16,A8+1,1)
Team off.....................................=B1

Some info places
FixtureListGenerator Info

Round Robin Tournament Scheduling

Golf League Schedule Templates

This is an individual movement from duplicate bridge -- maybe it can be adapted to 16 rounds for 24 players.

      -A- B- -C- -D- E F- -G- -H- I- J -K- -L- -M- -N- O -P- Q- -R- -S-
  1   16  1   6  10    4   5  13  3     8  15  12   9     7  2  11  14 
  2   16  2   7  11    5   6  13  4     9  15   1  10     8  3  12  14 
  3   16  3   8  12    6   7  13  5    10  15   2  11     9  4   1  14 
  4   16  4   9   1    7   8  13  6    11  15   3  12    10  5   2  14 
  5   16  5  10   2    8   9  13  7    12  15   4   1    11  6   3  14 
  6   16  6  11   3    9  10  13  8     1  15   5   2    12  7   4  14

As spreadcore says, this is, in general, a very hard problem.

Heres a quick try for you to see if this is close to what your looking for.

Its 24 players divided into 6 groups of 4 players each week for 20 weeks. Using a variation of the above formulas.