I was going through the questions on the Microsoft Excel Community, and one of the questions was on generating random numbers in Excel between 1 to 10, where none of the numbers repeat (i.e., there are no duplicates).
My first instinct was to use the RANDBETWEEN function.
I did a quick check with some random data in Excel, and it failed me – there were repetitions in the results.
Here is what I got when I used the RANDBETWEEN function:
So, I had to resort to the RAND function.
It works well with a negligible probability of the numbers repeating (I tested it multiple times on more than 100,000 cells, and there were no repetitions.
If you are generating a lot of random numbers, you can test it once).
Generating a Set of Unique Random Numbers in Excel
Here is how you can use the RAND function to generate a set of unique random numbers in Excel:
- In a column, use =RAND() formula to generate a set of random numbers between 0 and 1.
- Once you have generated the random numbers, convert it into values, so that it won’t recalculate again and again to make your workbook slow.
- In the adjacent column, use the following RANK formula and copy/drag for other cells (here I have used it for 10 numbers only, hence A2:A11. You can adjust the range accordingly).
=RANK(A2,$A$2:$A$11)
- That’s it. You would have a list of random numbers from 1 to 10, and none of the numbers would repeat.
NOTE: Remember to convert cells with RAND function from formula to values, else it will recalculate and change the list of Random Numbers every time you change anything in the workbook.
Do you use any other technique to generate random numbers with no duplicates?
Let me know in the comments section.
You May Also Like the Following Excel Tutorials:
16 thoughts on “How to Generate Unique Random Numbers in Excel (No Duplicates)”
I think RANK now contains a tiebreaker argument so you can guarantee a perfect set of unique numbers with your method.
Only way I found out to do this with zero chance of it repeating was with VBA…your solution probably will work if you’re only doing 10 or something like that.
If it doesn’t, you need to generate a random number, fill it in to the array, and for each next random figure you generate, check to see if it matches any figure in the array; if it does, generate a new number, if it doesn’t, store it and go to the next in the array.
I tested with 50,000 numbers and there is no repetition
To me, this is a very elegant solution that should work well more than 99% of the time. Thanks!
Hello, I need to know, how I can pick random unique no. in my excel. e.g. I have 2000 account and I need 100 account randomly. When I click one button. Highly appreciate your prompt response.
You want to select random records with a click. That would need VBA. Another way could be use randbetween function in a column and then sort the data based on it. Then you can select first 100 records and those would be random.
Another way to do this is join the result of 2/3 of function’s result using “&” sign. This will give more probability of getting unique numbers.
Sir, i have 1 to 10 numbers in column “A” as A1:A10. Then i want 1 number in B1 in one click, then 2 number in B1 in next click, then 3, then 4,…..then 10. But after 10 i want 1 numer in next click.
Is it possible in excel please reply me.
Hi Bhimrao,
try this macro (leave first cell blank (means take A2:A11 rather A1:10) and select B2 cell and run the macro)
Sub SerNum3()
If ActiveCell.Offset(-1, 0).Value < 10 Then
ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Value = 1
ActiveCell.Offset(1, 0).Select
End If
End Sub
Regards
Hareesh
Dear Sir, you give me answer but when we run macro then only in B2 cell
I have 1, then 2, then 3,…..then 10
and after 10 i have reapet from 1, then 2, then 3,…..then 10
again 1 to 10
again 1 to 10 only in B2 cell
Hi Bhimrao,
what i understood is “for every click excel has to display 1 to 10 numbers and after 10 it is again 1,2,3……..10.
when i checked with same macro it is running as said above.
please send a excel sheet (explaining u r requirement) to my mail “hareesh.nalubolu@gmail.com
i will try to get the answer
regards
Hareesh
This is how I do it (same principle)
1. Fill a range of cells with ascending numbers (unique for example: 1,2,3,4…)
2.The fill an adjacent range of cells with randomly generated numbers using RANDBETWEEN
3. Sort the entire range by the second column of random numbers. You will now have unique random numbers.
There is a chance that the RAND numbers will contain a repetition. Thus their RANK would repeat.
Regards
Brian
Hi Brian.. It should work fine on small data sets. I did some testing on large data sets as well and it came clean. But yes, as I mentioned in the article, there is still a chance, and it is good to check it once
My trick is to put the numbers you want to randomize in column A put the rand function beside them in column B then sort by column B.. Even if there might be a duplicate inColumn b column a will have none.
Good trick.. Thanks for sharing. It is a fine approach when you want the random numbers once. If you need to randomize again, you will have to sort it again.
Comments are closed.