Round Robin for 8-ball/pool: 10 Teams, 4 tables, meet 1/week, 2 BYES per week

[jamesbond_007]

Requirements:
1) Games are 8-ball/pool etc.
2) Venue has only 4 tables
3) Two players (or Teams) at each table
4) Play frequency: all players meet once / week
5) Due to the time to play one match, only 8 teams can play each week.
    Two teams must sit out (Byes?) each week.
6) All teams must play each other the same number of times per session/round
7) Need to balance assignment of Home/Away for all teams, as best as possible
8_Would like to balance, as best as possible, teams playing on each table
    Although one would think that all tables are the same, there are 3 good tables and 
    one bad beer soaked/stained table.
9) It is OK to have a "last week" where less than 8 teams play, in order to prevent the session from becoming inordinately long (total weeks).
10) Would like to be able to extend to "N" rounds. 
      This can be as simple as just repeating round N as round N+1, bit it would be preferred to 
      be able to mix things up a bit, so that if there were any inequities experienced by a team in
      round N, they might be prevented in round N+1 (Perhaps just taking the schedule from 
      round N and reassigning the teams?)

Attempts:
5 Tables, assign one as a " bye" table
I tried generating a schedule for 5 tables, assigning one of the tables as a "BYE" table, and having those players sit out. Unfortunately, the schedule did not balance teams by table, so had some teams sitting out too often.

4 Tables, assign one of the twice used tables as a " bye" table
I tried setting up a schedule for 4 tables, which used one table twice.  I tried to use one of the twice used tables as the two-team bye. Again, due to table assignment not being balanced, I had some teams sitting out more than others.

I am fairly skilled at MS Excel, but my issue is finding the right algorithm, rather than how to code it.

Your help is appreciated.
Thanks!!

[Ian Wakeling]

Can you play the whole round-robin which amounts to 45 games spread over 12 weeks?  In which case the following schedule might work.

(B D) (C I) (J F) (A H)
(B G) (E A) (H I) (F C)
(I E) (D F) (A J) (G H)
(I F) (B H) (C D) (J G)
(D J) (I A) (F B) (H E)
(E J) (G C) (H D) (A F)
(C A) (J B) (E G) (D I)
(C H) (G D) (E F) (A B)
(F G) (I J) (B C) (D E)
(H J) (C E) (G A) (I B)
(A D) (F H) (G I) (J C)
(E B) (---) (---) (---)

The teams occur 2 or 3 times in each column (pool table).  This was made by randomly assigning the 45 games to the grid and then swapping pairs of games around to try to keep the teams in each round unique and balance the columns.  Afterwards I swapped around the left and right teams in each game to improve H/A balance.

Hope that helps.

[jamesbond_007]

I appreiate your help, Ian. 

That may work. 

Let me generate some statistics, and I'll let you know. 

I appreciate you taking time to manually "swap" items for balance. 
Can you suggest any algorithms that might help address this ? 

[jamesbond_007]

I ended up with the "2-round"  schedule.
Stats are listed.
All teams play each other TWICE.





Since Ian was so helpful to me, I thought I'd share the
process and testing I used hoping it might help someone else.

For those of you that know how to do this already, please skip over this.


DESIGN STRATEGY
1) Use the original 1-Round Schedule (from Ian) to form 2 sections (colorded Gray and Peach on the Schedule)
    + an odd week 
2) Identify the BYE matches (colored in YELLOW)
3) When building Section 2
     a) Reverse the Home and Visitor teams from Round 1 (Seems an easy way to balance out Home and Visitors
         for the teams across two rounds)
     b) Find a team that you can "swap" with another team to allow the odd week from Round 1 to merge with the
         odd week from Round 2 and put them at the end of Round 2.  Keep watch on the # Byes and ensure there are 
         no extra matches generated. 
         This was done to keep the scheule full during the session, and only alter it with extra "BYES"  on the last week.
4) Analyze each section independently
    This prevented duplication of finding issues. What issues I found in Round 1, I also need to fix in Round 2.
5) Tabulate the statistics from both sections independently and as a whole (total Round 1 and Round 2)
6) Try, as best as possible, to get the TOTALS of the statistics to be the same for all teams.
    I decided I was OK with allowing the teams to have a different number for a stat in Round 1 than Round 2
    as long as the totals came out pretty even.
7) Use Excel's Conditional Formating to alert out of bounds Conditions to my attention 
     EX. In the "Check All Values Used Once" any thing that was not a 1 was highlgted in RED.

STATISTICS USED
1) #Byes/Rd. - Counted all Byes for a team for Round 1, and seperately for Round 2
2) #Matches/Team - Counted all Matches for a team for Round 1, and seperately for Round 2 regardless of Home/Visitors
3) #Homes/Team - Counted all Matches for a team for Round 1 as Home Team, and seperately for Round 2
4) #Visitors/Team - Counted all Visitirs for a team for Round 1 as Home Team, and seperately for Round 2
5) Check All Values Used Once - For a given week, make sure that all Teams were no more or no less tha once
                             Counted each team in the weekly schedule and put it in a table.
6) Fair Number of Matches - Ensure that all teams paly each other twice.
                      a) Built a Data Table of all the matches (excluding Byes)  X vs Y
                      b) Removed Home/Visitors assignments and disregard Table #
                          I modified the list so that  X vs Y was not viewed as Y vs X.
                          That is, I chose to sort so that the first Team always alphabetically preceeded the 2nd team.
                          EX. in Y vs  X , I would chage it to X vs Y
                      c) Use an Excel Pivot table to
                           i) show the match relationships "X vs Y" 
                           ii) Count those relationships
                           iii) Ensure everyone was = 2 
                    

NOTE: Sorry for the large tables, I didn't want to supply screencaps nor modify the HTML to make the tables smaller.




STATS





Example of using Conditional Formatting for Out of Bounds conditions
Here we see A was used twice and G was not used.  (Need to change an A to a G to fix it).




MATCH LIST DATA TABLE (exceprt)





MATCH LIST PIVOT TABLE (Excerpt)