6 rinks, 12 teams to play on different rounds -using formulas or macro

[Tigermush]

Hi had this problem for a while and its causing me sleepless nights. Say we have 6 rinks and 12 teams (a, b, c , etc) Each team has to play each other but are not allowed to play on the same rink twice, In addition, they can not play the same opponent on the same round. Ive done the first couple of rounds as an example but would love to know if there is a code or a formula(s) that could solve this, and produce random different results every time.
I realise this sounds confusing I apologise if I have not explained myself too clearly.
I attach the sample file

kind Regards Tony

 

[vletm]

@Tigermush
Ex A-team should play total 11 games - okay
but can use only once every 6 rinks - okay.
That means A-team should play
... against two teams in same time in 5 games.

So far no need formula nor macro?

 

[AhmedAbbas]

Hi Mate,

I looked into your file and you have Two Control requirements as I can see
1. Team can't play on same ground again
2. No Same opponent in the same Round
Unfortunately as the matrix structure can't be same for both control requirement, this means you either need a VBA code or you need two control matrix. I have gone with the second approach.
Just an FYI Control for Team Duplication Match1 Column is blank as Fault can only happen from match 2 onwards. I hope this helps you a bit what you are looking for I did changed your data structure a little bit but that should not cause any problem

 

[Tigermush]

Hi Mate,

I looked into your file and you have Two Control requirements as I can see
1. Team can't play on same ground again
2. No Same opponent in the same Round
Unfortunately as the matrix structure can't be same for both control requirement, this means you either need a VBA code or you need two control matrix. I have gone with the second approach.
Just an FYI Control for Team Duplication Match1 Column is blank as Fault can only happen from match 2 onwards. I hope this helps you a bit what you are looking for I did changed your data structure a little bit but that should not cause any problem

Hi I think you've done it - you are a very clever man!! I Love Excel but I do struggle. Ive just done a quick test and I think it works.

 

[Tigermush]

Hi I think you've done it - you are a very clever man!! I Love Excel but I do struggle. Ive just done a quick test and I think it works.

Im going to try a few more tests -many many thanks

 

[AhmedAbbas]

Glad to be of any assistance. Do let me know if you find sone issues

 

[vletm]

@Tigermush
If case is really
there are 12 teams,
everyone plays to each other ...
that means 11 games per team - okay?
How many unique rinks have to be
if any team can use only once one rink?

 

[Tigermush]

Hi i think there is a little problem with one of the controls. I do think you have more or less solved the problem but im not sure if the Control for TEAM dupication is working, RiNK dupilcation seems fine - its still very good but love it if both controls were working .Any help would be great
Kind Regards Tony

 

[Tigermush]

@Tigermush
If case is really
there are 12 teams,
everyone plays to each other ...
that means 11 games per team - okay?
How many unique rinks have to be
if any team can use only once one rink?

Hi thanks for pointing this out . I think they will be on the same rink only twice

 

[Tigermush]

Hi I think you've done it - you are a very clever man!! I Love Excel but I do struggle. Ive just done a quick test and I think it works.

Hi Mate,

I looked into your file and you have Two Control requirements as I can see
1. Team can't play on same ground again
2. No Same opponent in the same Round
Unfortunately as the matrix structure can't be same for both control requirement, this means you either need a VBA code or you need two control matrix. I have gone with the second approach.
Just an FYI Control for Team Duplication Match1 Column is blank as Fault can only happen from match 2 onwards. I hope this helps you a bit what you are looking for I did changed your data structure a little bit but that should not cause any problem

Hi thanks so much for looking at this ... its just about exactly what i want , but i think one of the Controls is not working? I could be wrong.

i think there is a little problem with one of the controls. I do think you have more or less solved the problem but im not sure if the Control for TEAM dupication is working, RiNK dupilcation seems fine - its still very good but love it if both controls were working .Any help would be great
Kind Regards Tony

 

[vletm]

Tigermush ...
You think they will be on the same rink only twice ... hmm?
How do You think that six rinks and can use two times ...
Have You solved how many plays are there? 12?

 

[AhmedAbbas]

Hi thanks so much for looking at this ... its just about exactly what i want , but i think one of the Controls is not working? I could be wrong.

i think there is a little problem with one of the controls. I do think you have more or less solved the problem but im not sure if the Control for TEAM dupication is working, RiNK dupilcation seems fine - its still very good but love it if both controls were working .Any help would be great
Kind Regards Tony

Hi Mate,
You need to show me where you encountered the problem, from there i can help