SOLVED

Random Number Generator without duplicates

Copper Contributor

Hi Everyone, 

 

So I'm trying to use Microsoft Excel in order to do a random draw for a golf competition. Random draws are as simple as they sound. Players will enter their names onto our online booking system. We then take the names off, number them 1-50 (Or however many entries there are) for example then generate 1 number at a time, crossing off the name drawn out then keep on drawing names out till everyone has been drawn. This way playing partners will be completely random. Aside from the fact that it's a slightly time consuming process a random number generator will sometimes bring up the same number twice. 

 

I'd like to use Excel to import the names from our online booking system (Saves writing them down each time) and then use a formula to randomly pick out individuals but each time removing that player. This way after the first draw the random generator will only be selecting from 49 names instead of 50. Then I could simply drag the formula into the desidered cells and complete the entire draw in seconds. 

 

Any help would be much appreciated. I'm available to answer any further questions as well

 

Kind regards, 

 

Chris 

6 Replies
A quick way to generate Randon unique number is using the new array formulas such as :
=SORT(UNIQUE(RANDBETWEEN(SEQUENCE(50,1,1,1),70)))
You can make some arrangements to exclude or include numbers with that approach.
Hi Juliano,

Thanks for your prompt reply. I'll have to give this a test.

Would I have to amend the formula each time to exclude the numbers generated though?

@ChrisBentham Don't know what online booking system you have, but it should not be a problem to import a list from it. Then just add a random number in the next column and sort that random number in any order. Now, just pick your groups of 2, 3 or 4 players starting from the top.

 

Example attached.

 

Hi Riny,

Apologises if I'm being a bit simple. I'm far from an expert on Excel.

In the player column it has the player number in random orders. Have you just manually inputted that yourself? And how does the rand() function in the 2nd column help in this instance.

Kind regards,

Chris
best response confirmed by ChrisBentham (Copper Contributor)
Solution

@ChrisBentham No, I started off with a list of "names" Player1 to Player50. Then I added the formula =RAND() in C2 and then copied it down. Now you have several options. You can keep the column with the RAND() formula and sort it. That will result in some kind of random sort that you can redo if you don't like it. Or you copy-paste values these random numbers and then sort them in ascending or descending order. It will, either way, instantly create a list that you can pick your pairings from. I chose the latter option.

1 best response

Accepted Solutions
best response confirmed by ChrisBentham (Copper Contributor)
Solution

@ChrisBentham No, I started off with a list of "names" Player1 to Player50. Then I added the formula =RAND() in C2 and then copied it down. Now you have several options. You can keep the column with the RAND() formula and sort it. That will result in some kind of random sort that you can redo if you don't like it. Or you copy-paste values these random numbers and then sort them in ascending or descending order. It will, either way, instantly create a list that you can pick your pairings from. I chose the latter option.

View solution in original post