Random number Generator

[RayT]

Could you kindly assist me with a formula that generate 6 numbers between 1
and 49, without repeating any of the integers.
Thanks!

 

[Chip Pearson]

I don't believe there is anyway to do this with a formula; you'll need some
VBA code. See http://www.cpearson.com/Excel/randomNumbers.aspx for quite a
few choices. Look at the section entitled "Getting An Array Of Unique,
Non-Duplicated Value" and the UniqueRandomLongs function.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[RayT]

Thanks, for takin the time. I will try that? Cheers!

 

[RagDyer]

This should work for you:

In an out-of-the-way location, say Column Z, enter the Rand function,
In Z1 enter
=Rand()
And copy down to Z49.

Then enter this formula wherever you wish:

=INDEX(ROW($A$1:$A$49),RANK(Z1,$Z$1:$Z$49))

Copy down as many rows as you need random numbers.

Each time you hit <F9>, you'll get a new random selection.

You might choose to place your calc mode into Manual, so that you don't
inadvertently refresh the list.

 

[Max]

One other play using formulas which might interest you ..

Illustrated in this "Ready-to-randomize" sample:
http://www.freefilehosting.net/download/3acjd
Randomize 1 - 49 into a 6 col x 8 row grid.xls

The set-up:
Numbers 1 - 49 to be randomized listed in A1:A49
In B1: =RAND()
In C1: =INDEX($A:$A,RANK(B1,B$1:B$49))
Copy B1:C1 down to C49

Place in say, E2:
=INDEX($C:$C,ROWS($1:1)*6-6+COLUMNS($A:A))
Copy E2 across to J2, fill down to J10. Clear F10:J10.
The grid E2:J9 returns 8 sets of 6 random numbers
from the source in col A. E10 returns the last element.

Press F9 to regenerate.

---

 

[RayT]

Hey Max thats some good stuff there man. Its almost the same as how RagDyer
would do, well explained, u make it sound easy. Thanks mate.

 

[RayT]

RagDyer, that was again well said, it seems quite simply, i guess if you know
your stuff. It liked it. Thank u all.

 

[RayT]

Using VBA is very interesting, i really would love to learn alot about that.
Enjoyed working through the steps in the example from the page you mentioned.
I sure would recommend anyone who loves to learn more to try it. I
personally enjoyed it. Guez there are many ways to skin a cat. Cheers!

 

[Max]

welcome, RayT

 

[RagDyer]

Thanks for your feed-back.

 

[curiousgeorge408]

In an out-of-the-way location, say Column Z, enter the Rand
function[.] In Z1 enter =Rand()[.] And copy down to Z49.

Then enter this formula wherever you wish:
=INDEX(ROW($A$1:$A$49),RANK(Z1,$Z$1:$Z$49))
Copy down as many rows as you need random numbers.

Can you please explain the theory of operation.

I agree that it works. But it seems that =RANK(Z1,$Z$1:$Z$49) would
suffice. RANK(Z1,$Z$1:$Z$49) seems to return the same thing that the
full INDEX expression returns. It seems that ROW($A$1:$A$49) is
always 1.

In any case, I agree with RayT that this is a clever, yet nicely
simple solution to the problem.

 

[curiousgeorge408]

In an out-of-the-way location, say Column Z, enter the Rand function[.]
In Z1 enter =Rand()[.] And copy down to Z49.

Then enter this formula wherever you wish:
=INDEX(ROW($A$1:$A$49),RANK(Z1,$Z$1:$Z$49))
Copy down as many rows as you need random numbers.

Can you please explain the theory of operation.

I agree that it works. But it seems that =RANK(Z1,$Z$1:$Z$49) would
suffice. RANK(Z1,$Z$1:$Z$49) seems to return the same thing that the
full INDEX expression returns. It seems that ROW($A$1:$A$49) is
always 1.

In any case, I agree with RayT that this is a clever, yet nicely
simple solution to the problem.

 

[curiousgeorge408]

In an out-of-the-way location, say Column Z, enter the Rand function[.]
In Z1 enter =Rand()[.] And copy down to Z49.
Then enter this formula wherever you wish:
=INDEX(ROW($A$1:$A$49),RANK(Z1,$Z$1:$Z$49))
Copy down as many rows as you need random numbers.

[....]
I agree that it works. But it seems that =RANK(Z1,$Z$1:$Z$49) would
suffice. RANK(Z1,$Z$1:$Z$49) seems to return the same thing that the
full INDEX expression returns. It seems that ROW($A$1:$A$49) is
always 1.

Only out of context. ROW($A$1:$A$49) returns the array {1,2,...,49}.
In the context of INDEX(), the RANK() result (1,2,...,49) is used to
index into that array.

In this context, I believe that using INDEX() and ROW() is redundant,
since the OP is interested in randomly choosing amount 1,2,...,49,
which is exactly what RANK() returns, given that Z1:Z49 contains
random values.

However, if the OP had been interested in, for example, randomly
selecting from the range 13-61, then perhaps INDEX(ROW($A$13:$A
$61),RANK(Z1,$Z$1:$Z$49)) could be used -- although, I believe that
12+RANK(Z1,$Z$1:$Z$49) would suffice.

On the other hand, if the OP had wanted to randomly select unique
values from A1:A49, then I believe INDEX($A$1:$A$49,RANK(Z1,$Z$1:$Z
$49)) could be used, copying down for as many selections as required.

RagDyer (or any other expert), please comment.

 

[RagDyeR]

In this particular case, you're right, the Rand() function would suffice.

However, I always suggest the combination with Index() as a more or less
*generic* solution, which can be understood and built on.

What if the OP *later* wanted numbers *other* then 1 to 49, say 10 to 58, or
100 to 148?

=INDEX(ROW($A$100:$A$148),RANK(Z1,$Z$1:$Z$49))

OR, say that the list of numbers are *not consecutive*, OR, say that a
random list of names is desired ... with the master list located at say J50
to J98:

=INDEX($J$50:$J$98,RANK(Z1,$Z$1:$Z$49))

So, you can see how easily a revision might be accomplished if the necessary
basic functions are presented at the outset.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

In an out-of-the-way location, say Column Z, enter the Rand
function[.] In Z1 enter =Rand()[.] And copy down to Z49.

Then enter this formula wherever you wish:
=INDEX(ROW($A$1:$A$49),RANK(Z1,$Z$1:$Z$49))
Copy down as many rows as you need random numbers.

Can you please explain the theory of operation.

I agree that it works. But it seems that =RANK(Z1,$Z$1:$Z$49) would
suffice. RANK(Z1,$Z$1:$Z$49) seems to return the same thing that the
full INDEX expression returns. It seems that ROW($A$1:$A$49) is
always 1.

In any case, I agree with RayT that this is a clever, yet nicely
simple solution to the problem.

 

[Max]

Sorry, just detected that the earlier sample contained an inadvertent error*

Here's the corrected version:
http://www.freefilehosting.net/download/3adkm
Randomize 1 - 49 into a 6 col x 8 row grid.xls

*The formulas in the output grid E2:J10 got messed up. To correct, just
re-copy E2 across/down to J10. Clear F10:J10. The grid E2:J9 will return 8
sets of 6 random numbers from the source in col A. E10 returns the last
element.

---