How to create a list of random unique numbers
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.
Press function key F9 to generate a new random sequence.
Array formula in A2:
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 […]
How to enter an array formula
 Select cell A2

Copy / Paste above array formula to formula bar
 Press and hold CTRL + SHIFT
 Press Enter
 Release all keys
Learn the basics of Excel arrays
Array formulas allows you to do advanced calculations not possible with regular formulas.
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)
How to use the COUNTIF function
Counts the number of cells that meet a specific condition.
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: 11ROW(A1) equals 10. (111=10)
Formula in cell A3: 11ROW(A2) equals 9. (112=9)
and so on..
Step 5  Create a random number
=LARGE(ROW($1:$10)*NOT(COUNTIF($A$1:A1, ROW($1:$10))), RANDBETWEEN(1,11ROW(A1)))
RANDBETWEEN(1,11ROW(A1))
becomes
RANDBETWEEN(1,111)
becomes
RANDBETWEEN(1,10)
and returns a random number between 1 and 10.
The RAND function calculates a random real number greater than or equal to 0 and less than 1. The function […]
Step 6  Select a random number in array
=LARGE(ROW($1:$10)*NOT(COUNTIF($A$1:A1, ROW($1:$10))), RANDBETWEEN(1,11ROW(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.
The SMALL function lets you extract a number in a cell range based on how small it is compared to the other numbers in the group.
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.
Extract unique distinct values from a multicolumn cell range
Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]Extract unique distinct values sorted based on sum of adjacent values
Table of Contents Filtering unique distinct text values and sort them based on sum of adjacent values Filtering unique distinct […]Select 6 unique and random numbers from 1 to 49 (Lottery) in excel
Introduction This article describes how to randomly select 6 unique numbers from 1 to 49. In a 6from49 lotto, a […]
How to transpose it into colomn?
Fajar,
Array formula in B2:
Cell A2 is empty. Copy cell B2 to cell range C2:O2.
@ Oscar
Thank you for your quick reply.
Your suggestion is definitely work.
Thank you.
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.
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)))
Hallo, What if my number of rows will vary and I do not want to manually change the formula on different sheets?
Adell,
Array formula in cell B5:
You still have to adjust one cell range (bolded).
Thank you. I managed to get it working with using the Index and And function, pointing to my "count' value of maximum number required and it works like a charm!
I have some official user list which i want to use in random page number wise like attached example.
Please help me asap.
https://lh4.ggpht.com/1vrCVTerrixozegO8AyvQzUd7SNCWPeCv1oHKRUbJbIZ3UodpI_U8LdnvUs75yWPD3lanbc=s160
I have tried changing this formula to give me a random list of numbers from 1189 but it doesn't work. Ideally what I want is a grid 15 by 27 full of numbers from 1405. I was going to use this formula to give a list and paste them into my grid. Can anyone help?
mATT
Array formula in cell A2:
Copy cell A2 and paste to cell range A3:A190.
See attached file:
uniquerandomlistofnumbers1189.xls
I know this post is a few years old. I need this exact solution, to randomly generate numbers without duplicates however in this case from 1 to 271. I have copied the above formula and the attached file to expand the range, though any modifications to either always results in #NUM error. Do you know why this is the case?
Following modified formula may help
={INDEX($B$17:$B$33, LARGE(MATCH(ROW($B$17:$B$33), ROW($B$17:$B$33))*NOT(COUNTIF($E$17:E21, $B$17:$B$33)), RANDBETWEEN(1, ROWS($B$17:$B$33)COUNTA($E$17:E21))))}
Notice counta used at the end. This formula can be used starting from any row whereas the default one had to start at row 2.
Please note that you have to modify cell references since I have directly copied from relevant places in my excel sheet. Inconvenience regretted.
In formula counta function has been used in place of count since the purpose was to use this formula to generate random list of alphanumeric codes from list in a cell range.
I cannot seem to configure this formula to draw from an adjustable range taken from two cells.
Ex: I want a list of unique random numbers between whatever is entered in cell A4(Min) and B4(Max).
Any help would be greatly appreciated.
Array formula in cell B3:
See attached file:
uniquerandomlistofnumberswill.xls
Thank you Oscar. This only seems to work if the minimum is very low. Once you reach a minimum number>6 it begins to have problems. I am hoping to begin with numbers in the 10003000 range.
I wonder if you are seeing a similar problem?
Will,
you are right!
See attached file:
uniquerandomlistofnumberswill2.xls
This is brilliant Oscar. Thank you.
Thanks for this tip, but for some weird reason I still got a few duplicates. Is there a simple method to generate unique random numbers in a column?
I have a list of 1000 names in column A, and would like to generate unique integer numbers in column B for each of those names.
Thanks
My guess is that the cell reference (bolded below) is not changed.
=LARGE(ROW($1:$10)*NOT(COUNTIF($B$2:B2,ROW($1:$10))),RANDBETWEEN(1,11ROW(A1)))
The array formula above is entered in cell range B3 and down as far as needed.
If you enter the formula in cell D4 and downwards, you must change the cell reference to $D$3:D3, like this:
=LARGE(ROW($1:$10)*NOT(COUNTIF($D$3:D3,ROW($1:$10))),RANDBETWEEN(1,11ROW(A1)))
Hi Will,
This is a formula I used, the formula was in B3 down to B45 (teh length of my worksheet), the maximum was calcultated in B1, that is the formula revers to B1.
Hope it helps
{=LARGE(ROW(INDIRECT("$1:$"&B$1))*NOT(COUNTIF($B$2:B2,ROW(INDIRECT("$1:$"&B$1)))),RANDBETWEEN(1,B$1+1ROW(B1))))}
Hi Will,
This formula i just posted, calculted, unique random numbers between 1 and X. Where the maksimum (X)changed on every schedule. My minimum is fixed at 1, but you can change the minimum to refer to a cell that indicated the minimum value/qty.
Good luck
Hi Adell,
I am having trouble changing the minimum value to anything but 1. It has a tendency to result in #NUM! or 0.
EX for 1020 if B1=20:
{=LARGE(ROW(INDIRECT("$10:$"&B$1))*NOT(COUNTIF($B$2:B2,ROW(INDIRECT("$10:$"&B$1)))),RANDBETWEEN(10,$B$1+1ROW(B1)))}
Can you tell me what I am doing wrong?
Thanks for all the help!
Hi Will,
The #num is usually, because you did not 'activate' the "string", that is the "{ }" in the beginning and end of the formula. Because there is more than one formula/statement that needs to be "true", before the calculation is done,you need to 'tell' excel to do 'all'. To do this (old fashioned way) you need to go to the beginning of your formula, before the "=" and hold down CTRL + SHFT + ENTER, then the "{}" will appear.
I had a look at the formula and have entered the minimum value into E2.
{=(LARGE(ROW(INDIRECT("$"&E$2&":$"&B$1))*NOT(COUNTIF($B$2:B2,ROW(INDIRECT("$"&E$2&":$"&B$1)))),RANDBETWEEN(E$2,B$1+1ROW(B1))))}.
see the indirect sections as well as the randbetween part, where it stipulated the minimum and maximum values.
hope this helps, if not, shout :) (I don't know if you can obtain my email address from the webmaster if you need to contact me directly) (I am in RSA and will be going offline within the next hour  weekend!  and will only be back on Monday)
Adell
Will,
I also see that on your formula, you 'left out' the first and last set of brackets, that also might be part of your initial error.
=RANDBETWEEN(TIME(8,0,0),TIME(9,45,0))
i have to maintain random time between this nut is is not working
mayur,
randbetween works only with whole numbers.
Try this: =RAND()*(TIME(9,45,0)TIME(8,0,0))+TIME(8,0,0)
Hi Frnd,
I want to get a random value between 120 but I not able to make any sucess ,I used the formula [=LARGE(ROW($1:$20)*NOT(COUNTIF($A$1:A1,ROW($1:$20))),RANDBETWEEN(1,21ROW(A1)))]
But it is not working.Please help me...
Manoj,
your formula works, did you enter it as an array formula?
I tried on various systems with above code but i still get same error as below :
#NUM!
Please help me as it is very urgent for me.
Hi,
How to do tht or how do i make an array, because i tried as u guided above , please help me as it is very urgent
Hi ,
in your initial formula, you have "[". it should be "{". enter your formula, without the brackets before the "=" and the end one. Then, go to the beginning of your formula, to the left of the "=" and simultaneously press Ctrl Shft Enter . the "{" brackets will appear and your value will appear. (formula will work)
Hi,
I have done asu said but i get the error as: #VALUE!
and do not get the number ,please help
I used the below formula:
{=LARGE(ROW($1:$20)*NOT(COUNTIF($A$1:A1, ROW($1:$20))),RANDBETWEEN(1,21ROW(A1)))}
Hi,
I have taken your formula, above and copied and pasted it into a new spreadsheet. took out the "{" and "}" and redid the CTRL SHFT ENTER to create the array/string formula and it works om my schedule. The only thing, that might through you out is the space between the comma and "ROW". go to the following link: https://speedy.sh/aMyaR/a.xlsx
You should be able to open it and see your formula working.
cheesh!.. sorry, it should be "throw you out" .... auto correct...
Hi ,
in your initial formula, you have "[". it should be "{". enter your formula, without the brackets before the "=" and the end one. Then, go to the beginning of your formula, to the left of the "=" and simultaneously press Ctrl Shft Enter . the "{" brackets will appear and your value will appear.
Hi,
I have done asu said but i get the error as: #VALUE!
and do not get the number ,please help
I used the below formula:
{=LARGE(ROW($1:$20)*NOT(COUNTIF($A$1:A1, ROW($1:$20))),RANDBETWEEN(1,21ROW(A1)))}
Manoj,
Did you manage to download the spreadsheet I uploaded? Are your formula now working?
Adell,
Thank you for yor support but I'm sorry as when I click on the given link from u ,I get security error n the the downlod link closes n not allowing me to download the file . Is it possible for you sir to mail me the file on the following email id : manoranjan.tiwari@bt.com
Regards
Manoj
Hi Adell,
Is it possible to make a randome number set thro entire sheet: i tried the following formula :
[=LARGE(ROW($1:$65535)*NOT(COUNTIF($C$1:C1,ROW$1:$65535))),RANDBETWEEN(1,65536ROW(C1)))]
but it seems not working:
whenever i try to edit your formulla n ammmend it according to my own chice , it gives error as "#NUM!" and when i pres ctrl+shift+enter , it gives this error "#VALUE!" , can you pls explain wht needs to be done to do it or can you pls make for me and send on : manoranjan.tiwari@bt.com
Thanks in advance.
Manoj,
You have a typo in your formula:
=LARGE(ROW($1:$65535)*NOT(COUNTIF($C$1:C1, ROW($1:$65535))), RANDBETWEEN(1, 65536ROW(C1)))
I tried your formula and it works but it is extremely slow. Do you have to use formulas?
Also remember that the formula creates unique distinct random numbers. If you are looking for only random numbers use this:
=randbetween(0, 65535)
How can I make it start at 0? i.e. random numbers from 0 to 10?
=randbetween(0,10)
Can you try that? I tried that beforehand and it doesn't work for me. :( Why would 1,11 be 110 and 0,10 be 0 to 10?
I also want to have them lower down than rows 211 but can't make that work either. I tried changing the ROW($1:$10) bits but doesn't seem to work.
me,
try this formula in cell B3:
[...] An array formula taken from here..... How to create a list of random unique numbers in excel  Get Digital Help  Microsoft Excel resource [...]
[...] A couple of links that may help you: Learn Excel 2010  "Random with No Repeats": Podcast #1471  YouTube How to create a list of random unique numbers in excel  Get Digital Help  Microsoft Excel resource [...]
I've 30 objects and 10 people, and I need to assign each person with randomly (unique) selected objects as a daily activity. Would you please suggest me on how I can do it using excel formulae?
Vijay,
See this post: Assign each person with randomly unique objects as a daily activity
[...] Vijay asks: [...]
why does the code change the values in Columns "B" and "C" when I enter text values and tab to the next column?
This is what I'm Using, and I only want to affect Column "A" values:
=LARGE(ROW($1:$1000)*NOT(COUNTIF($A$1:A4, ROW($1:$1000))), RANDBETWEEN(1,1000ROW(A4)))
Correction:
I should have copied the correct code (and lessened the number of tests):
=LARGE(ROW($1:$20)*NOT(COUNTIF($A$1:A1, ROW($1:$20))), RANDBETWEEN(1,20ROW(A1)))
And, I should've said that when I enter a value in any other column, it automatically assigns a new random number to the fields in column "A". how to do I get it to assign a random number to column "A"'s fields without being readjusted when I enter any other information in the other columns???
Hi Paul,
Random numbers mean excel chooses a random number every time you refresh. (enter, tab, move etc)
Please give me more information as to what info do you want in A, B and C?
Regards
Adell,
I was looking for a way to have excel create a unique random number (not duplicated) in Column "A" that is not influenced by another key stroke. So, for instance, let's say I am creating a patient record and I want to give their account a uniqe number (almost like a primary key in Access or SQL). I don't want that number to be repeated, or to change as I enter more information in the next columns....
You might, once the number has randomly been created make it a definite number and not a formula anymore by: copy, paste special, values.
Or, for a patient number, for instance is to use say the first 3 letters of their surname [use this formula is the surname is in column B =LEFT(B2,3)] with a number, starting at 00001 to infinity. Have excel check that this combination has not been used before?
Let me know if this would work for you.
I think the second option, in using the first 3 letters of the surname with a random number may be the best option in this case. How would you recommend going about doing that???
Paul,
Perhaps like this:
As per Oscar's formula, your numbers will be sequential, which is great, for record keeping and all rules and regulations, that I have ever come in contact with, regarding assigning of record numbers. If you combine your random number formula in stead of the countif formula, you will still have the issue of the numbers changing every time, unless you copy/paste/special after assigning a number. Good luck
Hi Oscar,
Excellent article  with the steps explained.
Only I could not understand why the randbetween is restricted to lesser and lesser values as the range progresses.
RANDBETWEEN(1,20ROW(A1)))
Request you to explain the logic.
Oscar 
Trying to convert this into a formula for creating a random list from another source. For example, if I have a list of the 50 U.S. states I'd like to create a random/unique list of x number of states. I'm using the position of the state in the INDEX to use as the random number but I'm having trouble in the COUNTIF statement to relate the prior state names I've returned to the "used" portion of the COUNTIF array.
GMF,
Generate unique random text strings
In Excel 2003, when I copy your formula into the formula bar, (cell A2), and use CTRLSHIFTENTER, I get #VALUE! for a result. When I copy the cell directly from your worksheet, (in that case cell B3), and paste it into a blank worksheet, it works fine. Examining that formula it shows #VALUE! for 'RANDBETWEEN(1,11ROW(A1))', but still works. If I click on the formula bar and then CTRLSHIFTENTER, that formula stops working and returns #VALUE!. I can work around it by replacing ROW(A1) by 1, ROW(A2) by 2, etc.; or 11ROW(A1) by 10,,etc. – but for sanity's sake, I would like to know what Excel magic you use to make your formula work. Thanks.
Hi Oscar,
I am trying to create a random list of of ten one digit numbers between 0 and 9. It gives me a formula error when I try to do it. Any assistance would be greatly appreciated.
Regards,
Gavin
Gavin,
Did you enter the formula as an array formula? (Instructions above)
Hi All,
Would really appreciate it if someone can help me.
I am trying to generate random numbers between 300 & 300.
The formula above seems to work for positive numbers only?
=LARGE(ROW($1:$300)*NOT(COUNTIF($A$1:A1, ROW($1:$300))), RANDBETWEEN(1,300ROW(A1)))
How can I make this formula generate negative and positive ( 300 to 300)
Thanks
Jack,
Yes, you are right.
Try this:
=LARGE(ROW($1:$600)*NOT(COUNTIF($A$1:A1, ROW($1:$600))), RANDBETWEEN(1,600ROW(A1)))300
Thanks Oscar you are the best.
I tried it but some numbers are omitted and others are repeated. Would it be okay to email you directly?
Thank you so much
Jack
Jack,
Sorry, you are right. Try this in cell A2:
=LARGE(ROW($1:$600)*NOT(COUNTIF($A$1:A1, ROW($1:$600)300)), RANDBETWEEN(1,600ROW(A1)))300
If this isn´t working, email me.
I am trying to generate a random sequence of 110 without duplicates, but I would like to separate the numbers into two parts such that the first 5 numbers are a random sequence of 15 and the last 5 numbers are a random sequence of 610. (So the random sequence would look like this for example: 3 2 4 1 5 / 9 8 6 10 7) Is this possible to do with a formula? Do you have any suggestions?
Thank you.
Amy,
I am afraid no, but it is possible with a custom function (vba).
Hey,
For some reason I'm having trouble transposing these formulas for my needs.
I need 2 sets of random numbers in a spreadsheet, if possible 09 (not 110)
One set starting in cell C2 and going across the columns to L2
The other set starting in B3 and going down to B12
Thanks!
I need two sets of 10 random numbers between 0 and 9 (included) if 0 isn't an option then 110 is ok...
cell ranges are C2:L2 and B3:B12
Any help would be much appreciated!
Actually make those cell ranges C3:L3 and B4:B14. Sorry just having trouble with this
Hello, how I can generate 100 random numbers from 11111 up to 55555, but the numbers must be unique and not contain (0,6,7,8,9)
Daniel,
I am not sure you can do that with an array formula. You need a custom function (vba).
Can you help me?
Thank you so much for this simple solution. It's been the final piece in creating a differentiated started for practicing mental calculations in maths. Here's the result: https://lttmaths.com/2014/02/16/differentiatedmentalcalculationstrategystarter/
Nyima,
I am happy you found it useful. I have made something similar before:
https://www.getdigitalhelp.com/2013/08/23/basicmathematicsinexcelforschoolchildren/
[…] produces calculations suitable for each strategy (and looking on the web for methods to produce unique random numbers in Microsoft Excel  apparently harder than it first sounds!) Also included is a MENTAL TEST SCORE SHEET to record […]
I would like to produce a list of random numbers (non repeating). the number needs to be 5 digits. The number cannot start with zero. So it can have all the digits 0  9 in it but cannot start with zero. How do I do this?
leslie,
Try this array formula in cell A2:
=LARGE(ROW($10000:$99999)*NOT(COUNTIF($A$1:A1, ROW($10000:$99999))), RANDBETWEEN(1,89999ROW(A1)))
Remember to adjust the bolded cell reference if you enter the array formula in another cell.