I had previously asked for a random number generator from within a range(Unique string values in range), say 1-40, but this time around I am looking for a way in which a list of "unique" numbers is randomly generated from a given set of values eg. 1,45, 23, 90, 23 etc.

Also, in the previous question, the program seems to generate the same random values every time it is executed and not saved.

  • Use the solution to the other answer to generate a random number between (in your example) 1 and 5; then pick the corresponding number out of your list (1,45, 23, 90, 23). – mpy Jul 8 '13 at 12:44

You should place list of unigue numbers in a column of cells. Use randbetween and index to pich the numbers. With unique numbers in A1:A10

=Index(A1:A10, RANDBETWEEN(1, 10))
  • This method only gives a random number from the given random sample.What I was looking for is a list of non-repeating, random arrangement of the given random sample. – Sray Jul 5 '13 at 7:00
  • not a formula, but put just a standard rand() in column next to your numbers. sort by the randoms and your unique items are randomly sorted. Can't think at moment of any formula in excel that can pick random numbers without replacement – Joop Jul 5 '13 at 9:29

A simple way to generate a "random arrangement" is just to sort your numbers randomly, e.g. with numbers in A1:A10 put this formula in B1 copied to B10


Now select A1:B10 and sort by column B - now you have A1:A10 randomly arranged - repeat sort for new random arrangement

If you want a formula then without using B1:B10 just Joop's formula in C1 to generate the first number and then put this formula in C2


confirmed with CTRL+SHIFT+ENTER and copied down to C10

Not the answer you're looking for? Browse other questions tagged or ask your own question.