how to generate random numbers in range 1:20 with unique results

[jmac1947]

Hi Team,

In an earlier life I was tasked with finding a "random" method of selecting two numbers from a "1 to 20" range so that the generated numbers can be applied to an set of people who will be partnered in a golf game draw.

It is only one draw per year so I don't care if the players have previously played together in past years.

I am hoping that the possible solution can easily be modified by a "passable knowledge level" person to be able to select a mystery "9" out of 18 holes that count for scores that particular round.

I am sure golfers in the forum will understand my questions, happy to explain further to anybody who isn't following my request

(btw: this is an issue only for the 20 guys who go away once a year to play golf, the world will not collapse if I have to draw numbers out of a hat, just looking for a slightly more elegant solution and I already have a few scoring macros so my first guess (but not only possibility) is VBA)

Jmac

[XOR LX]

Hi,

Sorry - so to clarify you simply want a list of random numbers from 1 to 20 with no duplication?

(And you're right - the golf stuff went straight over my head! )

Regards

[jmac1947]

Hi XOR LX,

There must be good golf courses in Yorkshire surely????

The golf bit was to make sure people didn't think I was trying to win the pools or a lottery (been reading too many threads in the water cooler perhaps )

Yes, I have 20 players that I am trying to form into teams so I need random numbers from 1 to 20 with no duplications as you correctly stated.

I would also hope that a little tweaking to that "code" would allow me to generate random numbers from 1 to 18 with no duplicates for another purpose (mystery 9) although I will only ever need the first 9 numbers generated.

As I said in OP, the old fashioned way would be to stick marbles / balls / cards into a hat and draw them out, I am trying to "blind the natives with black magic" so as to avoid any suggestions that the draw may be rigged in anyway..... can you believe some people would think that on a sporting trip

Jmac

[XOR LX]

There being good golf courses in Yorkshire and me understanding golf is not like a simple IF(TRUE formula!!

One way is to put, in A1:

=RANDBETWEEN(1,20)

Then in A2 and copy down to A20:

=INDEX(SMALL(IF(1-ISNUMBER(MATCH(ROW($1:$20),A$1:A1,0)),ROW($1:$20)),ROW(INDIRECT("1:"&20-COUNT(A$1:A1)))),RANDBETWEEN(1,20-COUNT(A$1:A1)))

though obviously VBA would give you a much simpler set-up.

Excel as "black magic", eh? Scary thought!

Regards

[jmac1947]

Hi XOR LX,

Thanks again for your reply.

I tried the suggestions but kept getting an error from the second formula (and it is too complicated for my poor old brain on a Sunday) so I took the "=RANDBETWEEN(1,20)" suggestion and started to play in VBA.

I managed to get a result using the following code block

Please Login or Register  to view this content.

I am happy to admit there may be 100's of better ways to skin this particular cat but I have what I need so I am happy (The key output for me in the sequence the numbers are drawn in)

A simple change to the range in the generate statement and my other issue is also resolved.

I am a bit pleased with myself for getting this far but I acknowledge the start your response gave me, thanks heaps

Jmac

[XOR LX]

Sorry. I should've of course pointed out that the second formula is an array formula**.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[jmac1947]

Hmmmmm

another lesson in how much I do not know about excel

@ XOR LX, I will play with that new knowledge

This forum give you so many new ideas, it is so helpful

thanks for your help

Jmac

[XOR LX]

Sure. And you're welcome!

[snb]

Keep it simple.
See the attachment. The resulting unique random numbers in column B.
Refresh using F9


If you prefer a 100% VBA approach:

Please Login or Register  to view this content.