Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
These may be helpful: stackoverflow.com/q/5753063/657668 superuser.com/q/277386/76571 –  Excellll Jul 3 '13 at 14:01
add comment

1 Answer

up vote 1 down vote accepted

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

share|improve this answer
    
Its maybe possible wit Excel functions only: =RANDBETWEEN(1,60) and maybe an array function? But I have no clue how to check for duplicates. –  nixda Jul 3 '13 at 11:22
1  
superuser.com/q/277386/76571 –  Excellll Jul 3 '13 at 14:02
    
Well, as you already mentioned in the comments, there's a small downside because of MOD. But its a good solution anyway. –  nixda Jul 3 '13 at 14:37
    
I tried the above mentioned steps but while executing, 9 cells are not filled and and then it crashes.. –  user235376 Jul 3 '13 at 16:21
    
I selected 6 x 10 cells (rows or columns) , my first value being 1 and end value being 60. A1 to F10 –  user235376 Jul 3 '13 at 17:24
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.