Random Number Generator without Repeat Numbers

[tronchetti]

I've created a range from G2-J5, 16 cells total to generate random numbers between 1 & 54 using "=RANDBETWEEN(1,54)". The problem is I'm getting duplicate numbers within the 16 cells. How can I modify the formula to eliminate duplicate numbers?

Thanks, Tim

 

[movieapple]

Some pages that may be useful for those wishing to do this without VBA code:

http://www.iansharpe.com/excel-random.php

http://www.mcgimpsey.com/excel/udfs/samplenoreplace.html

http://forums.whirlpool.net.au/archive/1375410

http://chandoo.org/wp/2011/05/04/dummy-data-random-functions/

http://www.onsitetrainingcourses.co...ique_random_numbers_in_excel_without_vba.html

Cheers!

 

[DownUnderling51]

Try putting numbers 1 thru 54 in Column A, then enter =RAND() in B1, and drag down to B54. Then sort columns A and B by Column B, and take the first 16 numbers in Column A. They will now be 16 randomly chosen numbers between 1 and 54.

Superb.