MS Excel: Unique random number generator within a range

06
2014-04
  • Questioner

    I searched for a similar question but couldn't find one..

    I want to generate 10 groups out of numbers ranging from 1-60 (including both), with each and every group containing random and non-repeating numbers. How can I do this in excel?

  • Answers
  • nixda

    How to use it

    1. Open Excel & VBA editor (Alt+F11)
    2. Insert the code below under Sheet1
    3. Go back to Excel and select your desired range to fill with random & non-repeating numbers
    4. Execute the macro (Alt+F8)

    Sub randomNumbers()
        Low = Application.InputBox("Enter first valid value", Type:=1)
        High = Application.InputBox("Enter last valid value", Type:=1)
        Selection.Clear
        For Each cell In Selection.Cells
            If WorksheetFunction.CountA(Selection) = (High - Low + 1) Then Exit For
            Do
                rndNumber = Int((High - Low + 1) * Rnd() + Low)
            Loop Until Selection.Cells.Find(rndNumber, LookIn:=xlValues, lookat:=xlWhole) Is Nothing
            cell.Value = rndNumber
        Next
    End Sub
    

    Excel file to proof

    I love those small and simple solutions so much


  • Related Question

    Excel unique value in range
  • Gok Demir

    Hi I need to ensure the values unique in range with if possible with a high performance way. Sample data as shown below:

     A                 B
    1 Serial From   -   Serial To
    2 364500            364600
    3 326900            326940
    4 120000            120300
    5 154700            154800
    6    460               480
    
    • Table is unsorted
    • There are two numbers in each row which always A is less than B
    • I always enter new values to next empty row

    There musnt be any number that match the any range above. If I enter 120150 and 120700 (because of 4th row) it must give error. How could I do that? I use Excel 2007. Thanks


  • Related Answers
  • dkusleika
    {=IF(SUM(((A6>=$A$1:A5)*(A6<=$B$1:B5))+((B6>=$A$1:A5)*(B6<=$B$1:B5)))>0,"OVERLAP","")}
    

    If you put this in C6 and you put your next numbers in A6 and B6, C6 will say 'overlap' if any do.

    Don't type the curly braces {}. Instead enter the formula using control+shift+enter, because it's an array formula.