Random Number Generation without Repetition?

[lukman4068]

hey guys,

here's my question -

is there any way of generating random numbers within a set range in ONE cell only without having any repetition until all the numbers have been cycled through once, after which the random selection starts again.

I've tried using RANDBETWEEN but whilst this does the random number generation in once cell only, it does repeat numbers. What I would like is a similar function which does not.

So if we have 1 as the lowest number, and 75 as the highest, it should be on the 76th number generated where we have the cycle of random generation start again.

Any ideas anybody? and the numbers do have to generate in the same cell each time only.

thanks!

 

[MyExcel]

Hi
see this Link
http://www.mrexcel.com/forum/showthread.php?t=222922

 

[pgc01]

Hi

The solution in the link that MyExcel posted was for writing a list of unique random numbers in a range.

In this case, that you want to loop randomly through a list of numbers without repeating them until you used them all, you must use vba.

Here's how I'd do it:
- generate the array
- sort the array in a random order (you can use an array of random numbers as key)
- create a button in the worksheet
- for each click of the button use the next element of the list
- once you have used all the numbers in the list, reshuffle the array

You can use an auxilliary range to store the list of numbers or store them in vba

HTH