MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Non repeating random number is set of 6 numbers


Posted by Gareth Hall on December 18, 2001 2:46 AM

Started playing around at work today with a lotto coupon generating program, got a little stuckj when it started repeating numbers in the same game, here is what I have so far. The game draws 6 numbers from a barrel o45 and then 1 number from a different barrel of 45, not important for my program but at least you know.

From the site
http://www.goldencasket.com/powerball/number_frequencies.asp

From A1 to A16 are the numbers 1 to 16, from B1 to B16 are the 16 most frequent numbers sorted in numerical order, in cells D1 to I16 I have the formula =RANDBETWEEN($A$1,$A$16) which picks a number between 1 and 16 and puts it in the cell, in cell K1 I have the formula =VLOOKUP(D1,$A$1:$B$16,2,TRUE)which associates the number from 1 to 16 with the actual game number I want to use. I then paste that across another 5 columns to give me 6 numbers to go on my coupon, trouble is sometimes there is a double in the 6 numbers and I obviously can't fill the coupon in like this, I have tried and If statement but just keep getting circular reference errors. I know I am probably doing this the long way and could probably be made a lot simpler, any help with just not getting repeating numbers would be appreciated.


Posted by Ian Mac on December 18, 2001 4:53 AM

I'll see if I've understood

Try this,

in column A rows 1 - 16 put:

=RAND()

in column B row 1 type (make sure the $'s are correct):

=RANK(A2,$A$1:$A$16)+COUNTIF(A$1:A2,A2)-1

This will give you a unique ranking of the rows in column A.

now you can just select any 6 cells in column B and they will be unique.

Does this help??

Ian Mac

Posted by Gareth Hall on December 18, 2001 3:51 PM

No, the formula is wrong in B16 I end up with =RANK(A17,$A$1:$A$16)+COUNTIF(A$1:A17,A17)-1 after dragging it down, there is no data in cell A17. I can see what you are trying to do and should be able to use that to fix my code, thanks.

Posted by Aladin Akyurek on December 18, 2001 4:12 PM

Gareth --

Just use the part

=RANK(A2,$A$1:$A$16)

Aladin

Posted by Gareth Hall on December 18, 2001 7:16 PM

=RANK(A1,$A$1:$A$16) works much better :) I'll let you know if we have a win.

Posted by Ian Mac on December 19, 2001 1:37 AM

I copied the formula from row 2 of the test I set up, BUT

I'd still use

=RANK(A1,$A$1:$A$16)+COUNTIF(A$1:A1,A1)-1

just in case by some sheer freak of nature (nah, Numbers) you get exactly the same rand(), then both the rankings will be the same.

Ian Mac