RandBetween without duplicating

[OldDj]

I posted this earlier and had several answers and appreciate them all,
but I was unable to get them to work in a way in which I needed. I have
attached a file showing what I have done and described below my
situation. I am a novice and any assistance would be appreciated.

I am trying to get a random 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.

Attachment filename: example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=422196

 

[Ken Wright]

Is there a reason for the numbers being spread out the way they are, and if so,
then what are the ranges for the other groups likely to be?

 

[Jason Morin]

My suggestion would be to list the numbers 1-75 in H1:H75.
Then put =RAND() in I1 and fill down. Now insert into each
cell:

A1: =H1
A3: =H2
B1: =H3
B2: =H4
C1: =H5
C3: =H6

Select columns H-I and sort ascending on column I.
Automate the sorting by recording a macro if necessary.

HTH
Jason
Atlanta, GA

 

[Mark Graesser]

OldDJ
Similar to Jason's suggestion you can use RANK to avoid the manual sorting requirement

set up this table on a seperate sheet, I'll use the name "Table" for this sheet

1) In A1 enter =RANK(B1,$B$1:$B$75

2) In B1 enter =RAND(

3) Copy both formulas down to row 7

4) In C1 through C75 enter the numbers 1 to 7

Now on your other sheet use
=VLOOKUP(1,Table!A1:C75,3,0
=VLOOKUP(2,Table!A1:C75,3,0
=VLOOKUP(3,Table!A1:C75,3,0
etc..

This will pull the number from column 3 (the 1 to 75) that has the ranking 1 for the first number, the ranking 2 for the second number. etc..

Good Luck
Mark Graesse
(e-mail address removed)

----- Jason Morin wrote: ----

My suggestion would be to list the numbers 1-75 in H1:H75.
Then put =RAND() in I1 and fill down. Now insert into each
cell

A1: =H
A3: =H
B1: =H
B2: =H
C1: =H
C3: =H

Select columns H-I and sort ascending on column I.
Automate the sorting by recording a macro if necessary

HT
Jaso
Atlanta, G

-----Original Message----
I posted this earlier and had several answers and appreciate them all
but I was unable to get them to work in a way in which I needed. I hav
attached a file showing what I have done and described below m
situation. I am a novice and any assistance would be appreciated

I am trying to get a random output of numbers between 1

and 75 in
different cells as belo1
67 18 7

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

these cells
used randbetween(1,75) which accomplishes what I'm looking for with th
exception that I of course will get duplicate numbers which I don'
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
numbers on 1 page. It doesn't matter if I have duplications from
group to the other, just not within a group. I hope this i
understandable. Any help with this will be much appreciated

Attachment filename:

example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=42219

 

[Ken Wright]

Oldj, I have a routine from Tom Ogilvy that generates random number listings
without repeat. I tweaked it a little to give me sets of numbers for a lottery
draw, and it also prompted me for how many set of numbers I wanted and how many
numbers to be included within each set, again, all numbers within a set to be
without duplication. I have tweaked it slightly to disable the prompts and have
it generate 6 sets of 7 numbers. I then simply married your sheet up to it and
linked the relevant cells. At the touch of a button you will have what you
want. If you would like a copy (I'll send you the original as well, in case it
is of any use), then give me an email address and I'll send it. Or just email
me at (e-mail address removed) removing the NOSPAM bit and I'll mail it
back by return.

Filesizes are both very small circa 30K each.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

Is there a reason for the numbers being spread out the way they are, and if so,
then what are the ranges for the other groups likely to be?

 

[OldDj]

Thanks to everyone that has replied. My schedule hasn't permited me to
work with any of these ideas yet, but I shall later this evening. Ken I
have sent you my email addy to reply to. Again Thanks! all and don't be
surprised if I come up with more questions.

 

[Ken Wright]

Just in case anyone else was interested, here is Tom's code with a few tweaks:-
Routine prompts you for the number of sets of data and how many numbers within
each set. No duplication within a set. Works great for lottery numbers.


Option Explicit

Sub DrawNumbers()
'If you want unique random numbers, i.e. you want to shuffle the numbers 1 to 49
'
Dim i, choice, balls(1 To 49)
Dim lngArr(1 To 49) As Long
Dim RwNdx1 As Long
Dim RwNdx2 As Long
Dim ColNdx As Long
Dim ColW As Long
Dim lrow As Long
Dim cnt1 As Long
Dim cnt2 As Long
Dim temp As Long
Dim Rng As Range
Dim ar As Range
Dim cell As Range

ColW = ActiveSheet.UsedRange.Column - 1 + _
ActiveSheet.UsedRange.Columns.Count

lrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

' Clear the existing data first
Range("A1", Cells(lrow, ColW)).ClearContents
Range("A1").Select

cnt1 = InputBox("How many sets of numbers do you want?")
cnt2 = InputBox("How many numbers in each set do you want?")

If cnt2 > 49 Then
MsgBox ("You have asked for more numbers than you are pulling from - Try
again")
Call DrawNumbers
End If

RwNdx1 = 2
RwNdx2 = cnt2 + 1

For ColNdx = 1 To cnt1
Randomize
For i = 1 To 49
balls(i) = i
Next
For i = 1 To 49
choice = 1 + Int((Rnd * (49 - i)))
temp = balls(choice)
balls(choice) = balls(50 - i)
balls(50 - i) = temp
Next

i = 0

With Cells(RwNdx1 - 1, ColNdx)
.Value = "Set" & ColNdx
.HorizontalAlignment = xlCenter
.Font.Bold = True
End With

Set Rng = Range(Cells(RwNdx1, ColNdx), Cells(RwNdx2, ColNdx))
For Each ar In Rng
For Each cell In ar
i = i + 1
cell.Value = balls(i)
Next
Next
Next ColNdx
Range("A1").Select
End Sub