unique random number between 1,10 in single array formula

The Randbetween function may generate duplicate value. if we used the formula

=Randbetween(1,10) it will generate random numbers between 1 and 10, if we applied the formula in ten cells there is a possibility the formula may generate duplicate value in the ten cells. To avoid this you can use the following formula

=ARRAYFORMULA(LARGE(ROW($1:$10)*NOT(COUNTIF($A$1:A1, ROW($1:$10))), RANDBETWEEN(1,11-ROW(A1))))

Copy cell A2 and paste down as far as needed.

Explaining the alternative array formula in cell A2

Step 1 – Create an array
ROW($1:$10) creates this array {1, 2, 3, 4, 5, 6, 7, 8, 9, 10}

Step 2 – Create a criterion to avoid duplicate numbers
COUNTIF($A$1:A1, ROW($1:$10)) makes sure no duplicate numbers are created. The formula has both absolute and relative cell references ($A$1:A1). When the formula are copied down to cell A3 the cell reference changes to $A$1:A2. The value in cell A2 can´t be randomly selected again.
In cell A2, COUNTIF($A$1:A1, ROW($1:$10)) creates this array: {0, 0, 0, 0, 0, 0, 0, 0, 0, 0}

Step 3 – Create a new dynamic array
ROW($1:$10)*NOT(COUNTIF($A$1:A1, ROW($1:$10))) creates this array in cell A2: {1, 2, 3, 4, 5, 6, 7, 8, 9, 10}
If the array formula  randomly selects the number 2 in cell A2, the formula in cell A3 creates this array: {1, 0, 3, 4, 5, 6, 7, 8, 9, 10}
Number 2 can´t be selected anymore.

Step 4 – Calculate the number range in Randbetween(bottom, top)
The bottom value is always 1. The top value changes depending on current cell.
In cell A2 the top value is 10.
In cell A3 the top value is 9
and so on..
Formula in cell A2: 11-ROW(A1) equals 10. (11-1=10)
Formula in cell A3: 11-ROW(A2) equals 9. (11-2=9)
and so on..

Step 5 – Create a random number
=LARGE(ROW($1:$10)*NOT(COUNTIF($A$1:A1, ROW($1:$10))), RANDBETWEEN(1,11-ROW(A1)))
RANDBETWEEN(1,11-ROW(A1))
becomes
RANDBETWEEN(1,11-1)
becomes
RANDBETWEEN(1,10)
and returns a random number between 1 and 10.

Step 6 – Select a random number in array
=LARGE(ROW($1:$10)*NOT(COUNTIF($A$1:A1, ROW($1:$10))), RANDBETWEEN(1,11-ROW(A1)))
becomes
=LARGE({1, 2, 3, 4, 5, 6, 7, 8, 9, 10}, RANDBETWEEN(1,10))
becomes
=LARGE({1, 2, 3, 4, 5, 6, 7, 8, 9, 10}, random_number) and returns a random number between 1 and 10.

Leave a comment