I NEED TO USE A FORMULA #1-39 TO REPEAT ONCE 5 AT A TIME

E

excel craze

Please help I NEED TO USE A FORMULA #1-39 TO REPEAT ONCE 5 AT A TIME

ex: 4-8-12-29-38
5-8-15-23-38
 
M

Max

excel craze said:
Please help I NEED TO USE
A FORMULA #1-39 TO REPEAT ONCE 5 AT A TIME
ex: 4-8-12-29-38
5-8-15-23-38
Click to expand...

One play ..

A sample construct available at:
http://www.savefile.com/files/4954034
Random Pick sets of 5 from 1 to 39.xls

Fill A1:A39 with the numbers: 1,2,3 ... 39
Put in B1: =RAND()
Put in C1: =INDEX(A:A,RANK(B1,$B$1:$B$39))
Select B1:C1, fill down to C39
(C1:C39 returns a full random shuffle of the numbers 1-39)

Then put in E1: =INDEX($C:$C,ROW(A1)*5-5+COLUMN(A1))
Copy E1 to I1, fill down to I8
Clear cell I8 (press delete key)

[E1:I8 basically re-lays what's in C1:C40 into a 8R x 5C grid]

E1:I7 will yield 7 random sets of
5 non-repeating numbers from 1 - 39
(E8:H8 returns the remaining 4 numbers)

Just press F9 key to re-generate
 
M

Max

You might also be interested in this sample file:
http://savefile.com/files/7565212
Randomization_Lotto_program.xls
(Save the file to disk and open from there)

The sample utilizes the RandLotto* UDF as the core functionality
*by Dave Hawley & JE McGimpsey
(From: http://www.ozgrid.com/VBA/RandomNumbers.htm)

---
In sheet: Draw,
just change the "Settings" to suit (From, To, Pick#)
and then click the "Draw" button
(One click draws one random set of numbers)

Results will be written in G2 down
Click "Reset" to clear col G
 
Top