How to generate non-repeating random numbers in a column wihtout VB

I have a column of 10 cells and would like to fill each cell with random numbers between 1 and 10 without any of these 10 numbers being generated more than once. Is that possible to do without VB?

Thank you.

http://answers.microsoft.com/en-us/o...a-61b68c9daafe

Hi,

No problem if you create a column next to the number of random numbers. Then sort by the random column. See the attached. No VBA needed. You do need to press F9 to refresh the Random numbers and then Sort by that column.

Thank you all for the advice. I can only have one column dedicated to random number generation, and have to paste it several hundred times, and also vary the number of rows from 10 through 40. So what looks to work best is the first answer with that complex formula in each cell. Thanks again.

Here's a copy of that post for historical record in case the link above dies. This generates 33 non-repeating random numbers:

------------------------------
You could use an array formula to achieve this:



Enter your heading in cell A1

Enter the formula =RANDBETWEEN(101,133) in cell A2

Enter this array formula in cell A3:

=LARGE(ROW($101:$133)*NOT(COUNTIF($A$2:A2,ROW($101:$133))),RANDBETWEEN(1,(133+2-101)-ROW(A2)))

(press CTRL+SHIFT+ENTER to enter this as an array formula)

Now copy cell A3 down for as many rows as you require values and they should all be unique.

You can substitute the 101 and 133 in the above formula for any maximum and minimum values you want to use.

-----------------------------------