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:

The numbers in cell range A2:A11 are random and between 1 and 10.

random-list-of-unique-numbers

Press function key F9 to generate a new random sequence.

Array formula in A2:

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

The following article demonstrates how to sort values in a column in a random order, using an array formula:

Sort a list in random order in excel

Overview The array formula in cell range C2:C6 creates a random list from the values in cell range A2:A6. Array […]

Comments(4) Filed in category: Excel, Random, Sort values

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

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

Comments(2) Filed in category: Count values, Excel

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)

COUNTIF function

Counts the number of times a value exists in a cell range.

Comments(5) Filed in category: Excel

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.

Excel RANDBETWEEN and RAND functions – How to generate random numbers and text

Microsoft Excel has two useful functions for generating numbers. The RAND function and RANDBETWEEN function. RAND() function returns a random […]

Comments(10) Filed in category: Excel

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.

SMALL function and LARGE function

This function lets you extract any number in a cell range based on sort rank.

Comments(12) Filed in category: Excel

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.