RandBetween Question

[OldDj]

I am trying to get a randon output of numbers between 1 and 75 in 7
different cells as below


23 39 2
14
67 18 73

Using cells A1, A3, B1, B2, B3, C1 and C3. In each of these cells i
used randbetween(1,75) which accomplishes what I'm looking for with the
exception that I of course will get duplicate numbers which i don't
want. Is there a command I can use or do I have to do this differently?
Now to complicate the situation I am wanting six groups of the 7
numbers on 1 page. It doesn't matter if I have duplications from 1
group to the other just not within a group. I hope this is
understandable. Any help with this will be much appreciated.

 

[Norman Harker]

Hi OldDj!

RANDBETWEEN doesn't allow this but I've set up a workbook that will
achieve it for you.

It's based upon setting up a column with numbers 1:75 with =RAND() set
up against each number. You then sort the two columns of numbers based
upon the random number and that gives the numbers 1:75 in random
order. Take the top 7 and you have drawn 7 numbers from 75 without
replacement.

I use a subroutine to generate a set of 6 sets of random numbers with
each set drawing a fresh set of 7 form 75. This way you can't get the
same number in a set but different sets can contain the same number.

Feel free to write direct and I'll attach the workbook to an email.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Jack Sons]

Hi OldDj!

Alan Kennedy's code below is exactly what you need. When executed it first
asks how many numbers you need (in your case 7), then the lowest number in
the range to choose from (1), then it asks for the the highest number of
that range (75), after which it produces automatically the random selection
of 7 numbers out of 1 to 75.

Jack Sons
The Netherlands

Sub RandomNumbersXtoY()
'
' Randomselection Routine
' by Alan Kennedy
' This routine selects a user specified number of random selections
' from within a defined range.

'
Dim Numselect As Integer
Dim Minrange As Integer
Dim Maxrange As Integer
Dim checking As Boolean
Workbooks.Add
Sheets("Blad1").Select
Cells.Select
Selection.Clear
ActiveWorkbook.Names.Add Name:="numselect", RefersToR1C1:="=Blad1!R49C6"
ActiveWorkbook.Names.Add Name:="MINRANGE", RefersToR1C1:="=Blad1!R50C6"
ActiveWorkbook.Names.Add Name:="Maxrange", RefersToR1C1:="=Blad1!R51C6"
checking = "False"
While checking = "False"
Numselect = InputBox("enter Number of selections required")
Range("f49") = Numselect
Minrange = InputBox("enter Minimum number of range")
Range("f50") = Minrange
Maxrange = InputBox("enter Maximum number of range")
Range("f51") = Maxrange
If Numselect - 1 > Maxrange - Minrange Then
MsgBox ("You have made too many selections for this range,or your
minimum is larger than your maximum start again")
Else
checking = "true"
End If
Wend
Range("a1") = "=""You have requested ""&numselect&"" numbers between
""&MINRANGE&"" And ""&Maxrange"
Range("A3").Select
ActiveCell.FormulaR1C1 = "=aselecttussen(minrange,maxrange)"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
ActiveCell.Offset(1, 0).Range("a1").Select
ActiveCell.FormulaR1C1 = "=aselecttussen(minrange,maxrange)"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.FormulaArray =
"=IF(OR(R3c1:R[-1]C[-1]=RC[-1]),""Already"",""N"")"
For X = 1 To Numselect - 1
If ActiveCell = "Already" Then
ActiveCell.Offset(0, -1).Range("a1").Select
ActiveCell.FormulaR1C1 = "=aselecttussen(minrange,maxrange)"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.FormulaArray =
"=IF(OR(R3c1:R[-1]C[-1]=RC[-1]),""Already"",""N"")"
X = X - 1
Else
ActiveCell.Clear
ActiveCell.Offset(1, -1).Range("a1").Select
ActiveCell.FormulaR1C1 = "=round(RAND()*(maxrange-minrange)+minrange,0)"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.FormulaArray =
"=IF(OR(R3c1:R[-1]C[-1]=RC[-1]),""Already"",""N"")"
End If
Next X
ActiveCell.Offset(0, -1).Range("a1:b1").Select
Selection.Clear


End Sub

 

[Jumbo]

Hi,
I try this and it seems to work.

Using cells A1, A3, B1, B2, B3, C1 and C3. In each of these cells.

A1-->=IF(SUM($AA$1:$AA$7)<>7,INT(RAND()*75+1),A1)
A3-->=IF(SUM($AA$1:$AA$7)<>7,INT(RAND()*75+1),A3)
B1-->=IF(SUM($AA$1:$AA$7)<>7,INT(RAND()*75+1),B1)
B2-->=IF(SUM($AA$1:$AA$7)<>7,INT(RAND()*75+1),B2)
B3-->=IF(SUM($AA$1:$AA$7)<>7,INT(RAND()*75+1),B3)
C1-->=IF(SUM($AA$1:$AA$7)<>7,INT(RAND()*75+1),C1)
C3-->=IF(SUM($AA$1:$AA$7)<>7,INT(RAND()*75+1),C3)
Then in AA1 to AA7
AA1-->=COUNTIF($A$1:$C$3,A1)
AA2-->=COUNTIF($A$1:$C$3,A3)
AA3-->=COUNTIF($A$1:$C$3,B1)
AA4-->=COUNTIF($A$1:$C$3,B2)
AA5-->=COUNTIF($A$1:$C$3,B3)
AA6-->=COUNTIF($A$1:$C$3,C1)
AA7-->=COUNTIF($A$1:$C$3,C3)
When you are done go to Tools Menu/Options/Calculation and checkmark
iteration with a maximum iteration set to 100 or more then OK and Watch your
numbers, to do another set of numbers edit AA1 and Enter .

Just an idea nothing more.

 

[Jumbo]

Hi,
I like it, nicely done.
Thanks to you and Alan Kennedy.

Just an idea nothing more.
--
Jumbo

Hi OldDj!

Alan Kennedy's code below is exactly what you need. When executed it first
asks how many numbers you need (in your case 7), then the lowest number in
the range to choose from (1), then it asks for the the highest number of
that range (75), after which it produces automatically the random selection
of 7 numbers out of 1 to 75.

Jack Sons
The Netherlands

Sub RandomNumbersXtoY()
'
' Randomselection Routine
' by Alan Kennedy
' This routine selects a user specified number of random selections
' from within a defined range.

'
Dim Numselect As Integer
Dim Minrange As Integer
Dim Maxrange As Integer
Dim checking As Boolean
Workbooks.Add
Sheets("Blad1").Select
Cells.Select
Selection.Clear
ActiveWorkbook.Names.Add Name:="numselect", RefersToR1C1:="=Blad1!R49C6"
ActiveWorkbook.Names.Add Name:="MINRANGE", RefersToR1C1:="=Blad1!R50C6"
ActiveWorkbook.Names.Add Name:="Maxrange", RefersToR1C1:="=Blad1!R51C6"
checking = "False"
While checking = "False"
Numselect = InputBox("enter Number of selections required")
Range("f49") = Numselect
Minrange = InputBox("enter Minimum number of range")
Range("f50") = Minrange
Maxrange = InputBox("enter Maximum number of range")
Range("f51") = Maxrange
If Numselect - 1 > Maxrange - Minrange Then
MsgBox ("You have made too many selections for this range,or your
minimum is larger than your maximum start again")
Else
checking = "true"
End If
Wend
Range("a1") = "=""You have requested ""&numselect&"" numbers between
""&MINRANGE&"" And ""&Maxrange"
Range("A3").Select
ActiveCell.FormulaR1C1 = "=aselecttussen(minrange,maxrange)"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
ActiveCell.Offset(1, 0).Range("a1").Select
ActiveCell.FormulaR1C1 = "=aselecttussen(minrange,maxrange)"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.FormulaArray =
"=IF(OR(R3c1:R[-1]C[-1]=RC[-1]),""Already"",""N"")"
For X = 1 To Numselect - 1
If ActiveCell = "Already" Then
ActiveCell.Offset(0, -1).Range("a1").Select
ActiveCell.FormulaR1C1 = "=aselecttussen(minrange,maxrange)"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.FormulaArray =
"=IF(OR(R3c1:R[-1]C[-1]=RC[-1]),""Already"",""N"")"
X = X - 1
Else
ActiveCell.Clear
ActiveCell.Offset(1, -1).Range("a1").Select
ActiveCell.FormulaR1C1 = "=round(RAND()*(maxrange-minrange)+minrange,0)"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.FormulaArray =
"=IF(OR(R3c1:R[-1]C[-1]=RC[-1]),""Already"",""N"")"
End If
Next X
ActiveCell.Offset(0, -1).Range("a1:b1").Select
Selection.Clear


End Sub

 

[Jumbo]

Hi Norman,
would mind to send me some info on how you do it , I want to compare with my
own work the differences in formulae etc...If you want.
Thank you

Just an idea nothing more.
--
Jumbo

Hi OldDj!

RANDBETWEEN doesn't allow this but I've set up a workbook that will
achieve it for you.

It's based upon setting up a column with numbers 1:75 with =RAND() set
up against each number. You then sort the two columns of numbers based
upon the random number and that gives the numbers 1:75 in random
order. Take the top 7 and you have drawn 7 numbers from 75 without
replacement.

I use a subroutine to generate a set of 6 sets of random numbers with
each set drawing a fresh set of 7 form 75. This way you can't get the
same number in a set but different sets can contain the same number.

Feel free to write direct and I'll attach the workbook to an email.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Norman Harker]

Hi Jumbo!

Have done. It's a quick and dirty approach but seems to work OK.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Hi Norman,
would mind to send me some info on how you do it , I want to compare with my
own work the differences in formulae etc...If you want.
Thank you

Just an idea nothing more.