So i would like to create a league table that has the ability to generate a random (or certainly something that appears random possibly using RAND or RANDBETWEEN) set of plays/matches of singles, doubles and trebles.
So on my darts team there is the POSSIBILITY of 4 to 7 players. (THIS POSSIBILITY IS CAUSING ME HUGE ISSUES)
There are 5 singles games, 4 doubles games and 4 trebles games.
No 1 person can obviously be matched with them selves on either the doubles or trebles
The sheet i would like to make will be giving me a list of who is to play what match and with whom.
If i could guarantee there would always be 5 players, i can scrape by with a RAND function that i have used to create a unique identifier next to a list of names that i can then VLOOKUP to drop into a table, only problem is when i get more or less than this number, i am left with a match/game that has no second/third player (when there is only 4 players) or the potential for the 6th or 7th player to never be picked.
i hope this makes sense and someone can help as this is driving me insane.
i did find a formula (below) that kind of got me part way there, i thought, but there is no way to regulate how many times it will pick a certain name.
=INDEX($A:$A,RANDBETWEEN(1,COUNTA($A:$A)),1)
I would like to try and accomplish this without the use of code/vba and just with functions/formula as would like to to work on google sheet (dont shoot me, i just need it mobile as this will be altered/generated when i dont have my laptop, only my iphone) at a push i could use excel on my phone, its just that i have already created a second sheet for something else to do with the teams on google sheets.
So on my darts team there is the POSSIBILITY of 4 to 7 players. (THIS POSSIBILITY IS CAUSING ME HUGE ISSUES)
There are 5 singles games, 4 doubles games and 4 trebles games.
No 1 person can obviously be matched with them selves on either the doubles or trebles
The sheet i would like to make will be giving me a list of who is to play what match and with whom.
If i could guarantee there would always be 5 players, i can scrape by with a RAND function that i have used to create a unique identifier next to a list of names that i can then VLOOKUP to drop into a table, only problem is when i get more or less than this number, i am left with a match/game that has no second/third player (when there is only 4 players) or the potential for the 6th or 7th player to never be picked.
i hope this makes sense and someone can help as this is driving me insane.
i did find a formula (below) that kind of got me part way there, i thought, but there is no way to regulate how many times it will pick a certain name.
=INDEX($A:$A,RANDBETWEEN(1,COUNTA($A:$A)),1)
I would like to try and accomplish this without the use of code/vba and just with functions/formula as would like to to work on google sheet (dont shoot me, i just need it mobile as this will be altered/generated when i dont have my laptop, only my iphone) at a push i could use excel on my phone, its just that i have already created a second sheet for something else to do with the teams on google sheets.
