Multi rule based selection

Hello

maybe I am not in the right place to ask this question. If so, please redirect me to the correct page.
Also if there is an already existing topic that discuss same issue, I will be more than happy to be redirected there.

I would like to create an automated excel spreadsheet for our sport event. We have many different teams form different countries and some game officials also from different countries.



I would like to organize game referees based on their nationality, regardful to this rules:

1. Game referee can't be the same nationality as the both teams are.

2. Same game referee can't officiate two games at the same time.

3. Same game referee can't officiate more than two games after each other.

Please find an rough draft of the schedule below in the attachments.



Basically what I would like to achieve is, that excel chooses a game official from the bank of officials by respecting those rules.

Thanks for all your support and help

Klemzy

I don't quite understand your request, is it necessary animation file Results ? Or something else

Do you want to add more samples, especially if you request the following points, the examples are not illustrated

1. Game referee can't be the same nationality as the both teams are.

2. Same game referee can't officiate two games at the same time.

3. Same game referee can't officiate more than two games after each other.

Please watch this animation teaching file FILE name as S18.gif
After the file is downloaded and decompressed, you can use the left mouse button to click twice to watch the teaching file (video) or open it with explorer.

Ok, sorry I tought I was clear enough.
The problem is: how to not do everything manually? How to make this process of arrangment atumated, so every time I change the teams I get a valid result for the referees.


In column B you have national referees listed.
I want to arrange referees into the column F. One referee per each game. It's important that all three rules are met, otherwise the result is not valid.
It's not a problem if some referees won't be arranged.

I hope I am clear enough.

Hey JeteMc, thanks for your response.
The formula you provided works fine, only one problem appears:
This formula stops working, when you spend all the available referees. So when you make more games (you do more combinations - let's say you set another 5 more games) it does not fill the referee, because it used all of them.
See provided screenshot
Excel-screenshot.png
I know I did not specify this in the original post, for which I am sorry. I hope this does not complicate the formula too much.
Thanks for all your help

I copied as you said, but i get this error message:
Attachment 747945
I am attaching the updated spreadsheet, so you can try to do it yourself if you also get the same error.