Re: Random number macro
Victor Delta
news:7hh155d5tolk3knj5...@4ax.com...
> On Sun, 5 Jul 2009 15:06:19 +0100, "Victor Delta" <no...@nospam.com>
> wrote:
>
>>
>>"Victor Delta" <no...@nospam.com> wrote in message
>>news:eprLDCD$JHA....@TK2MSFTNGP02.phx.gbl...
>>> "Victor Delta" <no...@nospam.com> wrote in message
>>> news:uvYrvrB$JHA...@TK2MSFTNGP05.phx.gbl...
>>>>I would like to create an Excel macro which will put a series of random
>>>>integers (leaving values not the formulas) in cells in a column. It
>>>>should
>>>>start in the currently selected cell and fill downwards until it has
>>>>covered the number of cells given by the number in, say, cell P1. The
>>>>random integers should be between 1 and the number in cell P2
>>>>(inclusive).
>>>>
>>>
>>> Many thanks for all the helpful suggestions - I'll give them all a try.
>>>
>>> Mike H - many thanks for your second solution although actually my limit
>>> will be less than the range of cells so some random numbers will repeat
>>> and not be unique.
>>>
>>> By the way, should have told you that I'm actually using Excel 2003 -
>>> not
>>> sure I can see the RANDBETWEEN function?
>>>
>>> Thanks again,
>>>
>>> V
>>
>>Mike H and Lars-�ke,
>>
>>Many thanks for your macros - they both worked brilliantly and did exactly
>>what I asked for.
>>
>>However, after running them, I realised I had come up with the wrong
>>solution to my problem - what I need is a random sequence not random
>>numbers.
>>
>>The problem I am trying to address is table allocations for training
>>events
>>for large numbers of people. I had assumed that a series of random numbers
>>say between 1 and 6 would, with a large enough number of delegates, give
>>almost equal numbers on each table. However, your macros showed me that it
>>does not. Using this method I ended up with sometimes double the number of
>>people on one table over another! On reflection, I guess this is bound to
>>happen with truly random numbers! How to learn the hard way!
>>
>>So what would overcome this would be a macros which inserts say the
>>numbers
>>1-6 (i.e. P2) in a random sequence and then continues with further random
>>sequences until it has covered P1 number of cells.
>>
>>Is it possible to create a macro that will do this please?
>>
>>Once again, many thanks,
>>
>>V
>>
>>PS This approach will also ensure that there is the widest possible mix of
>>people form all parts of the list on each table.
>
>
> Here are two more.
>
> This shorter one distributes the table numbers making sure that there
> difference in number of participants per table is never more than one.
> If the number of particpants is a multiple of the number of tables,
> there will be the same number of participants on each table.
> However, there is no guarantee that the first P1/P2 number of
> participants on the list will not end up on the same table.
>
> Sub victor_delta2()
> Dim randoms() As Double
> number_of_persons = ActiveSheet.Range("P1").Value
> number_of_tables = ActiveSheet.Range("P2").Value
> ReDim randoms(number_of_persons)
> Randomize
> For i = 0 To number_of_persons - 1
> randoms(i) = Rnd()
> Next i
> For i = 0 To number_of_persons - 1
> min_rand = 1
> For j = 0 To number_of_persons - 1
> If randoms(j) < min_rand Then
> min_rand = randoms(j)
> minj = j
> End If
> Next j
> randoms(minj) = 1
> ActiveCell.Offset(minj, 0) = (i Mod number_of_tables) + 1
> Next i
> End Sub
>
> This longer one does the same, with the addition that there is a
> guarantee that in the first, second, third, etc sequence of P2
> participants on the list, they will be distributed on all P2 tables
> (which is what you asked for I guess)
>
> Sub victor_delta3()
> Dim randoms() As Double
> number_of_persons = ActiveSheet.Range("P1").Value
> number_of_tables = ActiveSheet.Range("P2").Value
> ReDim randoms(number_of_tables)
> Randomize
> base = 0
> While base < number_of_persons
> For i = 0 To number_of_tables - 1
> randoms(i) = Rnd()
> Next i
> For i = base To base + number_of_tables - 1
> min_rand = 1
> For j = 0 To number_of_tables - 1
> If randoms(j) < min_rand Then
> min_rand = randoms(j)
> minj = j
> End If
> Next j
> randoms(minj) = 1
> If base + minj < number_of_persons Then
> ActiveCell.Offset(base + minj, 0) = (i Mod number_of_tables) + 1
> End If
> Next i
> base = base + number_of_tables
> Wend
> End Sub
>
> Hope this helps / Lars-�ke
Lars-�ke
Many thanks - you are an absolute star! Both macros work very well but, as
you said, the second now does exactly what I was looking for. Problem
solved.
Thanks again,
V
=====================================
The last macro above (Sub victor_delta3()) achieved exactly what I wanted.
However, for various reasons, I need to make a couple of alterations and
wondered if anyone could help please?
Firstly I would like the random number generator to start in a particular
cell (say D5) rather than the selected cell.
Secondly, the column to the left (col C) has some rows marked with an X in
the appropriate cell. Would it be possible for the macro to only enter its
random numbers in the rows that have an X in Col C please?
TIA
V
JLatham
add-in. To gain access to it: Tools --> Add-Ins and tick the 'Analysis
Toolpak' option. Might as well tick the 'Analysis Toolpak - VBA' option
also, as some of us around here use some of those features also in our coding.
"Victor Delta" wrote:
> "Lars-Åke Aspelin" <lar...@REMOOOVE.telia.com> wrote in message
> news:7hh155d5tolk3knj5...@4ax.com...
> > On Sun, 5 Jul 2009 15:06:19 +0100, "Victor Delta" <no...@nospam.com>
> > wrote:
> >
> >>
> >>"Victor Delta" <no...@nospam.com> wrote in message
> >>news:eprLDCD$JHA....@TK2MSFTNGP02.phx.gbl...
> >>> "Victor Delta" <no...@nospam.com> wrote in message
> >>> news:uvYrvrB$JHA...@TK2MSFTNGP05.phx.gbl...
> >>>>I would like to create an Excel macro which will put a series of random
> >>>>integers (leaving values not the formulas) in cells in a column. It
> >>>>should
> >>>>start in the currently selected cell and fill downwards until it has
> >>>>covered the number of cells given by the number in, say, cell P1. The
> >>>>random integers should be between 1 and the number in cell P2
> >>>>(inclusive).
> >>>>
> >>>
> >>> Many thanks for all the helpful suggestions - I'll give them all a try.
> >>>
> >>> Mike H - many thanks for your second solution although actually my limit
> >>> will be less than the range of cells so some random numbers will repeat
> >>> and not be unique.
> >>>
> >>> By the way, should have told you that I'm actually using Excel 2003 -
> >>> not
> >>> sure I can see the RANDBETWEEN function?
> >>>
> >>> Thanks again,
> >>>
> >>> V
> >>
> >>Mike H and Lars-Åke,
> > Hope this helps / Lars-Åke
>
> Lars-Åke
>
> Many thanks - you are an absolute star! Both macros work very well but, as
> you said, the second now does exactly what I was looking for. Problem
> solved.
>
> Thanks again,
>
> V
> =====================================
>
> The last macro above (Sub victor_delta3()) achieved exactly what I wanted.
>
> However, for various reasons, I need to make a couple of alterations and
> wondered if anyone could help please?
>
> Firstly I would like the random number generator to start in a particular
> cell (say D5) rather than the selected cell.
>
> Secondly, the column to the left (col C) has some rows marked with an X in
> the appropriate cell. Would it be possible for the macro to only enter its
> random numbers in the rows that have an X in Col C please?
>
> TIA
>
> V
>
> .
>
Victor Delta
V
"JLatham" <JLa...@discussions.microsoft.com> wrote in message
news:F179849F-8D4D-474A...@microsoft.com...
JLatham
turkey and I'll see what I can do with it in the next short while. Lots to
digest with all the >> >>> >>> >>>>>> in the message.
"Victor Delta" wrote:
> .
>
Victor Delta
V
"JLatham" <JLa...@discussions.microsoft.com> wrote in message
news:81B57038-B9EF-4A03...@microsoft.com...
Lars-�ke Aspelin
wrote:
>"Lars-锟絢e Aspelin" <lar...@REMOOOVE.telia.com> wrote in message
>news:7hh155d5tolk3knj5...@4ax.com...
>> On Sun, 5 Jul 2009 15:06:19 +0100, "Victor Delta" <no...@nospam.com>
>> wrote:
>>
>>>
>>>"Victor Delta" <no...@nospam.com> wrote in message
>>>news:eprLDCD$JHA....@TK2MSFTNGP02.phx.gbl...
>>>> "Victor Delta" <no...@nospam.com> wrote in message
>>>> news:uvYrvrB$JHA...@TK2MSFTNGP05.phx.gbl...
>>>>>I would like to create an Excel macro which will put a series of random
>>>>>integers (leaving values not the formulas) in cells in a column. It
>>>>>should
>>>>>start in the currently selected cell and fill downwards until it has
>>>>>covered the number of cells given by the number in, say, cell P1. The
>>>>>random integers should be between 1 and the number in cell P2
>>>>>(inclusive).
>>>>>
>>>>
>>>> Many thanks for all the helpful suggestions - I'll give them all a try.
>>>>
>>>> Mike H - many thanks for your second solution although actually my limit
>>>> will be less than the range of cells so some random numbers will repeat
>>>> and not be unique.
>>>>
>>>> By the way, should have told you that I'm actually using Excel 2003 -
>>>> not
>>>> sure I can see the RANDBETWEEN function?
>>>>
>>>> Thanks again,
>>>>
>>>> V
>>>
>>>Mike H and Lars-锟絢e,
>> Hope this helps / Lars-锟絢e
>
>Lars-锟絢e
>
>Many thanks - you are an absolute star! Both macros work very well but, as
>you said, the second now does exactly what I was looking for. Problem
>solved.
>
>Thanks again,
>
>V
>=====================================
>
>The last macro above (Sub victor_delta3()) achieved exactly what I wanted.
>
>However, for various reasons, I need to make a couple of alterations and
>wondered if anyone could help please?
>
>Firstly I would like the random number generator to start in a particular
>cell (say D5) rather than the selected cell.
>
>Secondly, the column to the left (col C) has some rows marked with an X in
>the appropriate cell. Would it be possible for the macro to only enter its
>random numbers in the rows that have an X in Col C please?
>
>TIA
>
>V
Here is a new version of the macro that will take care of the first
alteration and, maybe, the second alteration.
You have to state cleary what your purpose with the "X"es are.
Is it just to "surpress" the display in column D? All P1 number will
then NOT be displayed.
This is what the macro victor_delta4() does.
Or is it to "move" the display by inserting blank cells where you
don't have an "X" but still have all the P1 numbers displayed?
This is not done by macro victor_delta4().
Sub victor_delta4()
Set start_cell = ActiveSheet.Range("D5")
Dim randoms() As Double
number_of_persons = ActiveSheet.Range("P1").Value
number_of_tables = ActiveSheet.Range("P2").Value
ReDim randoms(number_of_tables)
Randomize
base = 0
While base < number_of_persons
For i = 0 To number_of_tables - 1
randoms(i) = Rnd()
Next i
For i = base To base + number_of_tables - 1
min_rand = 1
For j = 0 To number_of_tables - 1
If randoms(j) < min_rand Then
min_rand = randoms(j)
minj = j
End If
Next j
randoms(minj) = 1
If base + minj < number_of_persons Then
If start_cell.Offset(base + minj, -1).Value = "X" Then
start_cell.Offset(base + minj, 0) = (i Mod number_of_tables)
+ 1
End If
End If
Next i
base = base + number_of_tables
Wend
End Sub
Hope this helps / Lars-锟絢e
Victor Delta
Lars-锟絢e
Very many thanks for the new macro. I'll give it a try.
I'm sorry I did not make it totally clear what effect I wanted the Xs to
have - I was trying to keep things as brief and simple as possible, but had
overlooked the implications of doing this. My spreadsheet contains the all
the names of people who might attend one of my training events. In column C
I indicate with an X those who will be attending the next one - so P1 is the
number of Xs in col C.
What would be brilliant would be if the macro could run down column D but
only entering it's random (table) numbers in the cells of column D where
there is an X in col C.
I gather from what you say that the latest macro probably will not do this
but I'll give it a try.
Thank you so very much for all your help. It is much appreciated.
V
Lars-�ke Aspelin
wrote:
>"Lars-�ke Aspelin" <lar...@REMOOOVE.telia.com> wrote in message
>news:ardtg5ls8ai90u7sv...@4ax.com...
>> On Tue, 24 Nov 2009 20:58:07 -0000, "Victor Delta" <no...@nospam.com>
>> wrote:
>>
>>>"Lars-�ke Aspelin" <lar...@REMOOOVE.telia.com> wrote in message
>>>>>Mike H and Lars-�ke,
>>>> Hope this helps / Lars-�ke
>>>
>>>Lars-�ke
>> Hope this helps / Lars-�ke
>
>Lars-�ke
>
>Very many thanks for the new macro. I'll give it a try.
>
>I'm sorry I did not make it totally clear what effect I wanted the Xs to
>have - I was trying to keep things as brief and simple as possible, but had
>overlooked the implications of doing this. My spreadsheet contains the all
>the names of people who might attend one of my training events. In column C
>I indicate with an X those who will be attending the next one - so P1 is the
>number of Xs in col C.
>
>What would be brilliant would be if the macro could run down column D but
>only entering it's random (table) numbers in the cells of column D where
>there is an X in col C.
>
>I gather from what you say that the latest macro probably will not do this
>but I'll give it a try.
>
>Thank you so very much for all your help. It is much appreciated.
>
>V
Give this macro a try then;
Sub victor_delta5()
Set next_cell = ActiveSheet.Range("D5")
Dim randoms() As Double
Dim results() As Integer
number_of_persons = ActiveSheet.Range("P1").Value
ReDim results(number_of_persons)
number_of_tables = ActiveSheet.Range("P2").Value
ReDim randoms(number_of_tables)
Randomize
base = 0
While base < number_of_persons
For i = 0 To number_of_tables - 1
randoms(i) = Rnd()
Next i
For i = base To base + number_of_tables - 1
min_rand = 1
For j = 0 To number_of_tables - 1
If randoms(j) < min_rand Then
min_rand = randoms(j)
minj = j
End If
Next j
randoms(minj) = 1
If base + minj < number_of_persons Then
results(base + minj) = (i Mod number_of_tables) + 1
End If
Next i
base = base + number_of_tables
Wend
numbers_left = number_of_persons - 1
While numbers_left >= 0 And next_cell.Row < 1000
If next_cell.Offset(0, -1).Value = "X" Then
next_cell.Value = results(numbers_left)
numbers_left = numbers_left - 1
End If
Set next_cell = next_cell.Offset(1, 0)
Wend
End Sub
The "And next_cell.Row < 1000" should not be need. I just put it there
to avoid a lengthy loop endiing with a crash in the case that the
number of "X" in the C column (from row 5 and donw) is less than the
value in P1.
Victor Delta
Lars-�ke
That's absolutely fantastic! The macro works brilliantly - does just what I
wanted.
Thank you so much.
V