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

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:


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


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



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


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