How to create random match roles by category

Hi everyone,

I have a query to see if what I want to do is possible since I do not know if it can be done and how to do it if possible.

A client asks me for an app to manage a soccer league, where he automatically asks me for the app to perform the role of games between the registered teams, respecting the category of each team.

This means; There are 100 teams of different categories registered and, the application must segment by categories and among those teams of the same category choose at random the teams that will face each other throughout the different dates.

As an example in the following image


As shown in this category, do it with all the others.

For your support, thank you.

So I can’t really help in detail with the formulas, as I don’t know the data structure. But here’s my suggestions.

Assuming you have 20 teams, each team will play the other 19 teams once per seasson. I’d start off by creating 19 columns in the table where you store the teams.

In your new [Game 1] column we need to randomly pick 1 team to play against and it can’t be itself. So use a select function to get a list of the other 19teams, in the same category.

Then use INDEX(RANDBETWEEN(1,19),1) to get one of these 19 at random

In [Game 2] column you want to do largely the same but the Select() now needs to exclude that team and the team in [Game 1]. Also change this to INDEX(RANDBETWEEN(1,18),1)

This way the first column picks a team at random from 19, then the next column will pick at random from 18 and so on. If you want it to be truely random you have to use RANDBETWEEN() and not ANY() since the later will just pick the first match it finds. So if you ran a formula with ANY() twice you’d get the same result.

This will get a correct result for the first team. But when it moves onto team 2 the formulas will need to be aware of the match dates already picked with team 1, and so on.

Hope this helps :slight_smile:

1 Like

@1minManager Simon

First of all I want to thank you for taking the time to answer me.

The structure is not done since first I wanted to investigate if it is possible to do it, now that you have given me a solution, I will create the structure and test what you tell me.