How to create a list of random unique numbers in excel
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:
Formula in cell A2:
=INDEX(SMALL(IF(COUNTIF($A$1:A1, ROW($1:$10))=0, ROW($1:$10), ""), ROW(INDIRECT("1:"&SUM(IF(COUNTIF($A$1:A1, ROW($1:$10))=0, 1, 0))))), ROUND(RAND()*SUM(IF(COUNTIF($A$1:A1, ROW($1:$10))=0, 1, 0)), 0), ) + CTRL + SHIFT + ENTER copied down to cell A14.
UPDATE:
Alternative array formula in A2:
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.
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
IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition
SUM(number1,[number2],)
Adds all the numbers in a range of cells
SMALL(array,k) returns the k-th smallest row number in this data set.
ROW(reference) returns the rownumber of a reference
INDIRECT(ref_text,[a1])
Returns the reference specified by a text string







August 9th, 2011 at 3:17 pm
How to transpose it into colomn?
August 9th, 2011 at 8:06 pm
Fajar,
Array formula in B2:
Cell A2 is empty. Copy cell B2 to cell range C2:O2.
August 10th, 2011 at 6:21 am
@ Oscar
Thank you for your quick reply.
Your suggestion is definitely work.
Thank you.
August 30th, 2011 at 1:17 pm
The sample file works in Excel 2003 while pressing F9, but if using F2 and ctrl+shift+enter on B3 for example, the result is #VALUE! even if not changing anything.
Formula evaluation shows in Step 5: RANDBETWEEN(1,{10}) with 10 in {} that evaluates to #VALUE.
August 31st, 2011 at 6:32 am
As a workaround, I've added the first function that came to mind (SUM) to change the array with 1 item to a single value.
RANDBETWEEN(1,SUM(ROW(A1)))