Unique Random Number generator

Hello David,

Here's a way using a "helper" column.

Assume column A has integers from A1 down with no gaps
B1 has the number of numbers to generate. In the first row of another column, e.g. J1 put this formula

=RAND()

and copy down as far as you might have numbers in column A (doesn't matter if you go further)

Now in C1 use this formula copied down as many rows as the maximum value you might have in B1

=IF(ROWS(C$1:C1)>B$1,"",INDEX(A:A,RANK(J1,OFFSET( J$1,,,COUNT(A:A)))))

This should now give you your list of random numbers from column A

Any sheet re-calculation will re-generate the numbers (also press F9)

You might want to hide column J

Input formula in cell C2 and copy down.

Please Login or Register  to view this content.

The formula is an-array need to hold down:


Ctrl,Shift,Enter