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?
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this communityI 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?
Sheet1
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
^{I love those small and simple solutions so much}
=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
MOD
. But its a good solution anyway.
– nixda
Jul 3 '13 at 14:37
I thought I had an answer, but this was a dead end (but quite fun) and I don't know how to remove it. My need is to do bingo-sheets with random setups.
Make a column A4:A63 with the numbers 1 to 60. Enter a fairly big prime number less than 60 cell B1. For example '47`.
In B2 enter
=RANDBETWEEN(1;59)
In rows B4:B63 fill with
=MOD(((A4+B$2)*B$1);60)+1
=MOD(((A5+B$2)*B$1);60)+1
...
=MOD(((A63+B$2)*B$1);60)+1
This will make a fairly random sequence. B$2 will only shift the sequence.