How to create random match roles by category

[lefarole]

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.

[1minManager]

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.

help.appsheet.com

SELECT()

Gather column values from row in table or slice

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

Simon@1minManager.com

[lefarole]

@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.

Greetings.

[StephenMattison]

This is very similar to what I need, but I will not have a static 20 Members to choose from, so how do I do use RANDBETWEEN to choose one of the many Active Members in my Members Table, when this list can grow or shrink at any moment.

I want to optionally enable random Autofill of the [Organizer] for my Club Meetings app.

I have an [Autofill] column with one choice "Autofill". If "Autofill" is entered, then I want it to randomly pick from all Active Members.

I am already using this expression/formula in one of my Bots to send New Meeting email to the Email address of all Active Members.  SELECT(Active Members Slice[Email],([Club] = [_THISROW].[Club]))

So I think I'm half way there. I am thinking that I need a formula in my Initial Value area that says, if [Autofill]="Autofill" then do the SELECT & RANDBETWEEN.  How would this look? Could I do it all in one formula/step, or would I need a VC to pull the List of Members, then choose randomly from that column? 

I am trying this in my [Organizer] Initial Value, but it says I am using IF wrong.

IF([Autofill]="Autofill"),ANY(SELECT(Active Members Slice[Member],([Club] = [_THISROW].[Club])))

TYVM!

[Steve]

Re: How to ANY(SELECT randomly choose one value fr... - Google Cloud Community

[Swoopy]

This seems you would need looping commands and variables for temporary value storing that helps preventing duplicated random numbers which are then used in matching the teams. AppSheet has no such structural programming commands.