Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA:

and without enabling iterative calculation in excel options and not using "helper" columns?

Answer:

random-list-of-unique-numbers

Array formula in A2:

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

How to enter an array formula

  1. Select cell A2
  2. Copy / Paste above array formula to formula bar
    formula bar
  3. Press and hold CTRL + SHIFT
  4. Press Enter
  5. Release all keys

Copy cell A2 and paste down as far as needed.

Explaining 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.

How to customize array formula to your excel work sheet

If your list starts at F3 change $A$1:A1 to $F$2:F2 in the above array formula. To change the numbers from 1 to 10 to, for example, 2 to 12, change $1:$10 to $2:$12 also in the above array formula.
Press F9 to generate a new random list of unique numbers.

Download excel sample file for this tutorial.
unique-random-list-of-numbers.xls
(Excel 97-2003 Workbook *.xlsx)

Functions in this article:

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

LARGE(array,k)
returns the k-th largest number in this data set.

ROW(reference)
returns the row number of a reference

Related posts:

Select 6 unique and random numbers from 1 to 49 (Lottery) in excel

Create random dates, Mon to Fri, within a year in excel

Sort a list in random order in excel

Create a random playlist in excel

How to create random numbers, text strings, dates and time values