Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have the following formula in Excel (not vba):

=RANDBETWEEN(1,10)

Then I auto fill down 10 rows. How can I re-write the formula so the numbers don't repeat.

share|improve this question

migrated from stackoverflow.com Apr 30 '11 at 11:50

This question came from our site for professional and enthusiast programmers.

2  
You can't. A random number means you can't predict what the next one will be - a random number in such a tight range (1-10) is likely to have a repeated value. This question is off-topic here, anyway, since it's "(not VBA)". Voting to move to superusers – Ken White Apr 29 '11 at 17:01
    
You may actually want a random sort? If you're generating the numbers randomly, you could get the same value multiple times, in a sample of this size. Or, you could make a check to see what numbers have already been selected; I think you'd need a macro for that (VBA?). – Piskvor Apr 29 '11 at 17:02
    
You may as well use "CELL" and get the rownumber for each row if you want to have 10 unique numbers over 10 rows... – gbn Apr 29 '11 at 17:04
1  
@Ken, I agree that this question should be move to SuperUser, but realize that it doesn't have to be VBA to be here on SO. You can have questions on worksheet-functions that are appropriate. – Lance Roberts Apr 29 '11 at 17:09
2  
@Ken, Excel worksheet-functions are very often programming-syntax and development problems. They can get quite complex. – Lance Roberts Apr 29 '11 at 17:34
up vote 8 down vote accepted

There is a an easy way to do this with two columns. In A1, enter

 =RAND()

and fill down to A10.

In the adjacent column, enter

=RANK(A1,$A$1:$A$10)

and fill down. There is a negligible chance that of the 10 random floating point decimals (15 digits accuracy, I think), any two numbers will be the same. Thus, RANK will effectively always generate a random ordering of values from 1 to 10.

share|improve this answer
    
Since you are, in effect, using 10 random seeds (in A1:A10) to generate 10 numbers, the result should be just as random. Of course, the stipulation that no number is repeated in the final sequence reduces the randomness, to begin with. +1 – kaloyan Apr 29 '11 at 21:27

This previous answer does something similar: see how idx array is filled in the second example.

But it requires using VBA. I don't know if you want that or if you insist on using worksheet formulas only.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .