How to generate random non-repeat number for a range?

[niuyuer]

For example, I would like to assign a number to each cell of A2:A1001

There are 1000 cells, and I would like each cell to have a non-repeat random positive integer, which is in the range of [1, 1000].

I have to have them in random order, such as 458, 369, 567, ......, definitely not in any kinds of sorted order (1, 2, 3, 4, .....)


Thanks.

[ChemistB]

One option
On Sheet2, in A1:A1000
=RAND() (then copy >paste special>values so that doesn't change or change settings for this sheet to manual calcs)
For sheet1 in A2 copied down
=MATCH(SMALL(Sheet2!$A$1:$A$1000, ROWS($A$2:$A2), Sheet2!$A$1:$A$1000,0)
Does that work for you?

[JieJenn]

If you just want to perform a random sort, perhaps this macro might help. Basically the macro is going to create a list of random number in column B and then sort Column A and Column B by column B to generate the random order.

[Tony Valko]

Try this array formula**:

Entered in A2 and copied down.

=SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:1000")),A$1:A1,0)),ROW(INDIRECT("1:1000"))),RANDBETWEEN(1,1001-ROWS(A$2:A2)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

For 1000 rows this will be a bit slow to calculate.

[alansidman]

here is a VBA solution also

Option Explicit

Please Login or Register  to view this content.

[niuyuer]

Thanks, all.