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

Automatically Sort Data in Alphabetical Order using Formula. Random Group Generator Template. Change Negative Number to Positive in Excel [Remove Negative Sign] How to Stop Excel from Changing Numbers to Dates Automatically How to Shuffle a List of Items/Names in Excel? 2 Easy Formulas!

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

  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
  2. Sort the entire range by the second column of random numbers. You will now have unique random numbers.