Hi everyone,
I'm new and 1st I would like to say thanks for all experts that help us the newbies as me.
I am trying to solve a problem, and I would need your help!
I'll start run a project of 20 teams. This would be a matrix with 19 rounds played in 10 time slots.
And I would like to create a macro for a Round Robin completely balanced in Time Slot and home/away.
The conditions would be:
- Each team play one time and just one with all others, so 19 in total (this is the definition of round robin I think);
- All will play on system Home/Away each week, this means in the total 9/10 times to each;
- This Home/Away would be: one week home next week away, and at maximum could be 2 weeks away or 2 weeks home one following other 1 time;
- All teams should play in a balanced time slot. This would be a maximum of 1-2 in each time slot, being 2 times in 9 time slots and 1 time in 1 time slot;
I have investigated a lot and I found a lot of solutions.
But I could not find neither one that could aggregate all that have this "completely balanced round robin"
Maybe this is not possible.
But if someone have a clue, a Macro or a excel spreadsheet it would be very helpful and I would be ver grateful too.
Thank you in advance and once again thank you for your wonderful work.
The closest I got it was this:
[TABLE="width: 715"]
<tbody>[TR]
[TD="class: xl69, width: 65, align: center"][/TD]
[TD="width: 65"]Time Slot 1[/TD]
[TD="width: 65"]Time Slot 2[/TD]
[TD="width: 65"]Time Slot 3[/TD]
[TD="width: 65"]Time Slot 4[/TD]
[TD="width: 65"]Time Slot 5[/TD]
[TD="width: 65"]Time Slot 6[/TD]
[TD="width: 65"]Time Slot 7[/TD]
[TD="width: 65"]Time Slot 8[/TD]
[TD="width: 65"]Time Slot 9[/TD]
[TD="width: 65"]Time Slot 10[/TD]
[/TR]
[TR]
[TD="class: xl69, align: center"]Round 1[/TD]
[TD="class: xl69, align: center"]( T K )[/TD]
[TD="class: xl69, align: center"]( G R )[/TD]
[TD="class: xl69, align: center"]( P N )[/TD]
[TD="class: xl69, align: center"]( L D )[/TD]
[TD="class: xl69, align: center"]( A F )[/TD]
[TD="class: xl69, align: center"]( O S )[/TD]
[TD="class: xl69, align: center"]( M H )[/TD]
[TD="class: xl69, align: center"]( J Q )[/TD]
[TD="class: xl69, align: center"]( E B )[/TD]
[TD="class: xl69, align: center"]( C I )[/TD]
[/TR]
[TR]
[TD="class: xl69, align: center"]Round 2[/TD]
[TD="class: xl69, align: center"]( C S )[/TD]
[TD="class: xl69, align: center"]( D J )[/TD]
[TD="class: xl69, align: center"]( I H )[/TD]
[TD="class: xl69, align: center"]( G F )[/TD]
[TD="class: xl69, align: center"]( Q O )[/TD]
[TD="class: xl69, align: center"]( R L )[/TD]
[TD="class: xl69, align: center"]( P B )[/TD]
[TD="class: xl69, align: center"]( T A )[/TD]
[TD="class: xl69, align: center"]( M E )[/TD]
[TD="class: xl69, align: center"]( N K )[/TD]
[/TR]
[TR]
[TD="class: xl69, align: center"]Round 3[/TD]
[TD="class: xl69, align: center"]( Q N )[/TD]
[TD="class: xl69, align: center"]( M I )[/TD]
[TD="class: xl69, align: center"]( L P )[/TD]
[TD="class: xl69, align: center"]( E K )[/TD]
[TD="class: xl69, align: center"]( B S )[/TD]
[TD="class: xl69, align: center"]( A H )[/TD]
[TD="class: xl69, align: center"]( O R )[/TD]
[TD="class: xl69, align: center"]( D T )[/TD]
[TD="class: xl69, align: center"]( F J )[/TD]
[TD="class: xl69, align: center"]( G C )[/TD]
[/TR]
[TR]
[TD="class: xl69, align: center"]Round 4[/TD]
[TD="class: xl69, align: center"]( L C )[/TD]
[TD="class: xl69, align: center"]( T N )[/TD]
[TD="class: xl69, align: center"]( G I )[/TD]
[TD="class: xl69, align: center"]( O H )[/TD]
[TD="class: xl69, align: center"]( F B )[/TD]
[TD="class: xl69, align: center"]( E J )[/TD]
[TD="class: xl69, align: center"]( Q K )[/TD]
[TD="class: xl69, align: center"]( R M )[/TD]
[TD="class: xl69, align: center"]( A S )[/TD]
[TD="class: xl69, align: center"]( P D )[/TD]
[/TR]
[TR]
[TD="class: xl69, align: center"]Round 5[/TD]
[TD="class: xl69, align: center"]( S T )[/TD]
[TD="class: xl69, align: center"]( B M )[/TD]
[TD="class: xl69, align: center"]( F Q )[/TD]
[TD="class: xl69, align: center"]( H P )[/TD]
[TD="class: xl69, align: center"]( L E )[/TD]
[TD="class: xl69, align: center"]( K C )[/TD]
[TD="class: xl69, align: center"]( J G )[/TD]
[TD="class: xl69, align: center"]( I N )[/TD]
[TD="class: xl69, align: center"]( R D )[/TD]
[TD="class: xl69, align: center"]( A O )[/TD]
[/TR]
[TR]
[TD="class: xl69, align: center"]Round 6[/TD]
[TD="class: xl69, align: center"]( J M )[/TD]
[TD="class: xl69, align: center"]( S Q )[/TD]
[TD="class: xl69, align: center"]( B O )[/TD]
[TD="class: xl69, align: center"]( K G )[/TD]
[TD="class: xl69, align: center"]( I D )[/TD]
[TD="class: xl69, align: center"]( L A )[/TD]
[TD="class: xl69, align: center"]( H T )[/TD]
[TD="class: xl69, align: center"]( P R )[/TD]
[TD="class: xl69, align: center"]( N C )[/TD]
[TD="class: xl69, align: center"]( F E )[/TD]
[/TR]
[TR]
[TD="class: xl69, align: center"]Round 7[/TD]
[TD="class: xl69, align: center"]( O I )[/TD]
[TD="class: xl69, align: center"]( Q D )[/TD]
[TD="class: xl69, align: center"]( M C )[/TD]
[TD="class: xl69, align: center"]( A R )[/TD]
[TD="class: xl69, align: center"]( S J )[/TD]
[TD="class: xl69, align: center"]( F K )[/TD]
[TD="class: xl69, align: center"]( E N )[/TD]
[TD="class: xl69, align: center"]( G L )[/TD]
[TD="class: xl69, align: center"]( B H )[/TD]
[TD="class: xl69, align: center"]( T P )[/TD]
[/TR]
[TR]
[TD="class: xl69, align: center"]Round 8[/TD]
[TD="class: xl69, align: center"]( B G )[/TD]
[TD="class: xl69, align: center"]( F T )[/TD]
[TD="class: xl69, align: center"]( A M )[/TD]
[TD="class: xl69, align: center"]( J C )[/TD]
[TD="class: xl69, align: center"]( R N )[/TD]
[TD="class: xl69, align: center"]( H D )[/TD]
[TD="class: xl69, align: center"]( S P )[/TD]
[TD="class: xl69, align: center"]( K I )[/TD]
[TD="class: xl69, align: center"]( O L )[/TD]
[TD="class: xl69, align: center"]( E Q )[/TD]
[/TR]
[TR]
[TD="class: xl69, align: center"]Round 9[/TD]
[TD="class: xl69, align: center"]( E D )[/TD]
[TD="class: xl69, align: center"]( O C )[/TD]
[TD="class: xl69, align: center"]( T L )[/TD]
[TD="class: xl69, align: center"]( F N )[/TD]
[TD="class: xl69, align: center"]( J H )[/TD]
[TD="class: xl69, align: center"]( B R )[/TD]
[TD="class: xl69, align: center"]( A I )[/TD]
[TD="class: xl69, align: center"]( Q P )[/TD]
[TD="class: xl69, align: center"]( S K )[/TD]
[TD="class: xl69, align: center"]( M G )[/TD]
[/TR]
[TR]
[TD="class: xl69, align: center"]Round 10[/TD]
[TD="class: xl69, align: center"]( G H )[/TD]
[TD="class: xl69, align: center"]( P K )[/TD]
[TD="class: xl69, align: center"]( C R )[/TD]
[TD="class: xl69, align: center"]( M S )[/TD]
[TD="class: xl69, align: center"]( E A )[/TD]
[TD="class: xl69, align: center"]( Q B )[/TD]
[TD="class: xl69, align: center"]( T J )[/TD]
[TD="class: xl69, align: center"]( F O )[/TD]
[TD="class: xl69, align: center"]( L I )[/TD]
[TD="class: xl69, align: center"]( D N )[/TD]
[/TR]
[TR]
[TD="class: xl69, align: center"]Round 11[/TD]
[TD="class: xl69, align: center"]( I F )[/TD]
[TD="class: xl69, align: center"]( H L )[/TD]
[TD="class: xl69, align: center"]( R S )[/TD]
[TD="class: xl69, align: center"]( C E )[/TD]
[TD="class: xl69, align: center"]( T M )[/TD]
[TD="class: xl69, align: center"]( P O )[/TD]
[TD="class: xl69, align: center"]( D A )[/TD]
[TD="class: xl69, align: center"]( N B )[/TD]
[TD="class: xl69, align: center"]( G Q )[/TD]
[TD="class: xl69, align: center"]( K J )[/TD]
[/TR]
[TR]
[TD="class: xl69, align: center"]Round 12[/TD]
[TD="class: xl69, align: center"]( D B )[/TD]
[TD="class: xl69, align: center"]( I S )[/TD]
[TD="class: xl69, align: center"]( N J )[/TD]
[TD="class: xl69, align: center"]( P A )[/TD]
[TD="class: xl69, align: center"]( M Q )[/TD]
[TD="class: xl69, align: center"]( G E )[/TD]
[TD="class: xl69, align: center"]( K L )[/TD]
[TD="class: xl69, align: center"]( C F )[/TD]
[TD="class: xl69, align: center"]( T O )[/TD]
[TD="class: xl69, align: center"]( R H )[/TD]
[/TR]
[TR]
[TD="class: xl69, align: center"]Round 13[/TD]
[TD="class: xl69, align: center"]( K O )[/TD]
[TD="class: xl69, align: center"]( L B )[/TD]
[TD="class: xl69, align: center"]( J A )[/TD]
[TD="class: xl69, align: center"]( N M )[/TD]
[TD="class: xl69, align: center"]( C P )[/TD]
[TD="class: xl69, align: center"]( I T )[/TD]
[TD="class: xl69, align: center"]( R Q )[/TD]
[TD="class: xl69, align: center"]( E H )[/TD]
[TD="class: xl69, align: center"]( D G )[/TD]
[TD="class: xl69, align: center"]( S F )[/TD]
[/TR]
[TR]
[TD="class: xl69, align: center"]Round 14[/TD]
[TD="class: xl69, align: center"]( N A )[/TD]
[TD="class: xl69, align: center"]( R F )[/TD]
[TD="class: xl69, align: center"]( K B )[/TD]
[TD="class: xl69, align: center"]( D O )[/TD]
[TD="class: xl69, align: center"]( G T )[/TD]
[TD="class: xl69, align: center"]( C Q )[/TD]
[TD="class: xl69, align: center"]( I E )[/TD]
[TD="class: xl69, align: center"]( H S )[/TD]
[TD="class: xl69, align: center"]( P M )[/TD]
[TD="class: xl69, align: center"]( J L )[/TD]
[/TR]
[TR]
[TD="class: xl69, align: center"]Round 15[/TD]
[TD="class: xl69, align: center"]( M L )[/TD]
[TD="class: xl69, align: center"]( E P )[/TD]
[TD="class: xl69, align: center"]( O G )[/TD]
[TD="class: xl69, align: center"]( B I )[/TD]
[TD="class: xl69, align: center"]( H K )[/TD]
[TD="class: xl69, align: center"]( S N )[/TD]
[TD="class: xl69, align: center"]( F D )[/TD]
[TD="class: xl69, align: center"]( A C )[/TD]
[TD="class: xl69, align: center"]( J R )[/TD]
[TD="class: xl69, align: center"]( Q T )[/TD]
[/TR]
[TR]
[TD="class: xl69, align: center"]Round 16[/TD]
[TD="class: xl69, align: center"]( P J )[/TD]
[TD="class: xl69, align: center"]( C H )[/TD]
[TD="class: xl69, align: center"]( D K )[/TD]
[TD="class: xl69, align: center"]( T B )[/TD]
[TD="class: xl69, align: center"]( N L )[/TD]
[TD="class: xl69, align: center"]( M F )[/TD]
[TD="class: xl69, align: center"]( G S )[/TD]
[TD="class: xl69, align: center"]( O E )[/TD]
[TD="class: xl69, align: center"]( Q A )[/TD]
[TD="class: xl69, align: center"]( I R )[/TD]
[/TR]
[TR]
[TD="class: xl69, align: center"]Round 17[/TD]
[TD="class: xl69, align: center"]( R E )[/TD]
[TD="class: xl69, align: center"]( K A )[/TD]
[TD="class: xl69, align: center"]( H F )[/TD]
[TD="class: xl69, align: center"]( I Q )[/TD]
[TD="class: xl69, align: center"]( P G )[/TD]
[TD="class: xl69, align: center"]( D M )[/TD]
[TD="class: xl69, align: center"]( N O )[/TD]
[TD="class: xl69, align: center"]( B J )[/TD]
[TD="class: xl69, align: center"]( C T )[/TD]
[TD="class: xl69, align: center"]( L S )[/TD]
[/TR]
[TR]
[TD="class: xl69, align: center"]Round 18[/TD]
[TD="class: xl69, align: center"]( F P )[/TD]
[TD="class: xl69, align: center"]( A G )[/TD]
[TD="class: xl69, align: center"]( E T )[/TD]
[TD="class: xl69, align: center"]( Q L )[/TD]
[TD="class: xl69, align: center"]( K R )[/TD]
[TD="class: xl69, align: center"]( J I )[/TD]
[TD="class: xl69, align: center"]( B C )[/TD]
[TD="class: xl69, align: center"]( S D )[/TD]
[TD="class: xl69, align: center"]( H N )[/TD]
[TD="class: xl69, align: center"]( O M )[/TD]
[/TR]
[TR]
[TD="class: xl69, align: center"]Round 19[/TD]
[TD="class: xl69, align: center"]( H Q )[/TD]
[TD="class: xl69, align: center"]( J O )[/TD]
[TD="class: xl69, align: center"]( S E )[/TD]
[TD="class: xl69, align: center"]( R T )[/TD]
[TD="class: xl69, align: center"]( D C )[/TD]
[TD="class: xl69, align: center"]( N G )[/TD]
[TD="class: xl69, align: center"]( L F )[/TD]
[TD="class: xl69, align: center"]( M K )[/TD]
[TD="class: xl69, align: center"]( I P )[/TD]
[TD="class: xl69, align: center"]( B A )
[/TD]
[/TR]
</tbody>[/TABLE]
In this model:
- Each team play one time and just one with all others, so 19 in total (this is the definition of round robin I think); OK
- All will play on system Home/Away each week, this means in the total 9/10 times to each; OK
- All teams should play in a balanced time slot. This would be a maximum of 1-2 in each time slot, being 2 times in 9 time slots and 1 time in 1 time slot; OK
- This Home/Away would be: one week home next week away, and at maximum could be 2 weeks away or 2 weeks home one following other 1 time; THIS IS NOT OK
Thank you very much in advance
