How to randomly generate list of names that can vary in length


New Member
Sep 22, 2015
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.


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.

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...