Im using the following to produce a random picking of names in the B column.
=INDEX($B$1:$B$100,RAND()*100+1)
Each name is entered a different number of times in the B column, so that
the names entered the most have a higher probability if hitting.
What Id like to have is another cells/columns that will auto-enter the
names in the B column the proper # of times.
E.g. If I have the following:
R S
1 Steve 4
2 Tom 7
3 Don 9

So that Steve is entered in the B column 4 times, Tom 7 times, Don 9 times,
etc.
Is this possible ?

Thanks,

Steve

No need to create the repeated name list.

Try this:

R S
1 Steve 4
2 Tom 7
3 Don 9

In T1 enter 1

Enter this formula in T2 and copy down to T3

=S1+T1

Then:

=INDEX(R1:R3,MATCH(RAND()*SUM(S1:S3)+1,T1:T3))

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
I'm using the following to produce a random picking of names in the B
column.
=INDEX($B$1:$B$100,RAND()*100+1)
Each name is entered a different number of times in the B column, so that
the names entered the most have a higher probability if hitting.
What I'd like to have is another cells/columns that will auto-enter the
names in the B column the proper # of times.
E.g. If I have the following:
R S
1 Steve 4
2 Tom 7
3 Don 9

So that Steve is entered in the B column 4 times, Tom 7 times, Don 9
times,
etc.
Is this possible ?

Thanks,

Steve

Thanks,

But I'm getting a circular reference, because I don't think I quite
explained what I have very well. This formula that I'm using
- =INDEX($B$1:$B$100,RAND()*100+1)
Is in a totally different cell (G18), that is showing the result.
If I have the # 4 in the cell next to Steve [S1]( and that # will change
every week), and a # 7 in the cell next to Tom [S2] ( and that # will also
change every week), how can I get 4 Steve's in that B column, 7 Tom's in that
B column, etc.
And to have the differing amounts of Steve & Tom the next week ?

Thanks again,
Steve


"T. Valko" wrote:

No need to create the repeated name list.

Try this:

R S
1 Steve 4
2 Tom 7
3 Don 9

In T1 enter 1

Enter this formula in T2 and copy down to T3

=S1+T1

Then:

=INDEX(R1:R3,MATCH(RAND()*SUM(S1:S3)+1,T1:T3))

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
I'm using the following to produce a random picking of names in the B
column.
=INDEX($B$1:$B$100,RAND()*100+1)
Each name is entered a different number of times in the B column, so that
the names entered the most have a higher probability if hitting.
What I'd like to have is another cells/columns that will auto-enter the
names in the B column the proper # of times.
E.g. If I have the following:
R S
1 Steve 4
2 Tom 7
3 Don 9

So that Steve is entered in the B column 4 times, Tom 7 times, Don 9
times,
etc.
Is this possible ?

Thanks,

Steve

You don't need the column of repeated names but if you insist, see this:

http://tinyurl.com/2hajwo

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
Thanks,

But I'm getting a circular reference, because I don't think I quite
explained what I have very well. This formula that I'm using
- =INDEX($B$1:$B$100,RAND()*100+1)
Is in a totally different cell (G18), that is showing the result.
If I have the # 4 in the cell next to Steve [S1]( and that # will change
every week), and a # 7 in the cell next to Tom [S2] ( and that # will also
change every week), how can I get 4 Steve's in that B column, 7 Tom's in
that
B column, etc.
And to have the differing amounts of Steve & Tom the next week ?

Thanks again,
Steve


"T. Valko" wrote:

No need to create the repeated name list.

Try this:

R S
1 Steve 4
2 Tom 7
3 Don 9

In T1 enter 1

Enter this formula in T2 and copy down to T3

=S1+T1

Then:

=INDEX(R1:R3,MATCH(RAND()*SUM(S1:S3)+1,T1:T3))

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
I'm using the following to produce a random picking of names in the B
column.
=INDEX($B$1:$B$100,RAND()*100+1)
Each name is entered a different number of times in the B column, so
that
the names entered the most have a higher probability if hitting.
What I'd like to have is another cells/columns that will auto-enter the
names in the B column the proper # of times.
E.g. If I have the following:
R S
1 Steve 4
2 Tom 7
3 Don 9

So that Steve is entered in the B column 4 times, Tom 7 times, Don 9
times,
etc.
Is this possible ?

Thanks,

Steve

Improvement:

=INDEX(R1:R3,MATCH(RAND()*SUM(S1:S3)+1,T1:T3))

If you're using Excel 2003 or earlier and have the analysis ToolPak add-in
installed or, if you're using Excel 2007:

=INDEX(R1:R3,MATCH(RANDBETWEEN(1,SUM(S1:S3)),T1:T3 ))

For any version:

=INDEX(R1:R3,MATCH(ROUND(RAND()*SUM(S1:S3),0)+1,T1 :T3))


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
No need to create the repeated name list.

Try this:

R S
1 Steve 4
2 Tom 7
3 Don 9

In T1 enter 1

Enter this formula in T2 and copy down to T3

=S1+T1

Then:

=INDEX(R1:R3,MATCH(RAND()*SUM(S1:S3)+1,T1:T3))

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
I'm using the following to produce a random picking of names in the B
column.
=INDEX($B$1:$B$100,RAND()*100+1)
Each name is entered a different number of times in the B column, so that
the names entered the most have a higher probability if hitting.
What I'd like to have is another cells/columns that will auto-enter the
names in the B column the proper # of times.
E.g. If I have the following:
R S
1 Steve 4
2 Tom 7
3 Don 9

So that Steve is entered in the B column 4 times, Tom 7 times, Don 9
times,
etc.
Is this possible ?

Thanks,

Steve

Thanks for all your patience. I surely didn't mean to insist that I wanted
that list, I was just adding that to the original random solution that was
provied to me by this group a few years ago. With the Excel 2207 solution
below, it works like a charm, and like you stated, a much cleaner and
improved solution.

Thanks much,

Steve

"T. Valko" wrote:

Improvement:

=INDEX(R1:R3,MATCH(RAND()*SUM(S1:S3)+1,T1:T3))

If you're using Excel 2003 or earlier and have the analysis ToolPak add-in
installed or, if you're using Excel 2007:

=INDEX(R1:R3,MATCH(RANDBETWEEN(1,SUM(S1:S3)),T1:T3 ))

For any version:

=INDEX(R1:R3,MATCH(ROUND(RAND()*SUM(S1:S3),0)+1,T1 :T3))


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
No need to create the repeated name list.

Try this:

R S
1 Steve 4
2 Tom 7
3 Don 9

In T1 enter 1

Enter this formula in T2 and copy down to T3

=S1+T1

Then:

=INDEX(R1:R3,MATCH(RAND()*SUM(S1:S3)+1,T1:T3))

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
I'm using the following to produce a random picking of names in the B
column.
=INDEX($B$1:$B$100,RAND()*100+1)
Each name is entered a different number of times in the B column, so that
the names entered the most have a higher probability if hitting.
What I'd like to have is another cells/columns that will auto-enter the
names in the B column the proper # of times.
E.g. If I have the following:
R S
1 Steve 4
2 Tom 7
3 Don 9

So that Steve is entered in the B column 4 times, Tom 7 times, Don 9
times,
etc.
Is this possible ?

Thanks,

Steve

I should have explained this in my original reply.

In essence the formula works as though each name was repeated n number of
times. From reading your post it seemed that you wanted the list with
repeats just for this single specific purpose. I didn't know whether the
list was used for other things.

Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
Thanks for all your patience. I surely didn't mean to insist that I wanted
that list, I was just adding that to the original random solution that was
provied to me by this group a few years ago. With the Excel 2207 solution
below, it works like a charm, and like you stated, a much cleaner and
improved solution.

Thanks much,

Steve

"T. Valko" wrote:

Improvement:

=INDEX(R1:R3,MATCH(RAND()*SUM(S1:S3)+1,T1:T3))

If you're using Excel 2003 or earlier and have the analysis ToolPak
add-in
installed or, if you're using Excel 2007:

=INDEX(R1:R3,MATCH(RANDBETWEEN(1,SUM(S1:S3)),T1:T3 ))

For any version:

=INDEX(R1:R3,MATCH(ROUND(RAND()*SUM(S1:S3),0)+1,T1 :T3))


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
No need to create the repeated name list.

Try this:

R S
1 Steve 4
2 Tom 7
3 Don 9

In T1 enter 1

Enter this formula in T2 and copy down to T3

=S1+T1

Then:

=INDEX(R1:R3,MATCH(RAND()*SUM(S1:S3)+1,T1:T3))

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
I'm using the following to produce a random picking of names in the B
column.
=INDEX($B$1:$B$100,RAND()*100+1)
Each name is entered a different number of times in the B column, so
that
the names entered the most have a higher probability if hitting.
What I'd like to have is another cells/columns that will auto-enter
the
names in the B column the proper # of times.
E.g. If I have the following:
R S
1 Steve 4
2 Tom 7
3 Don 9

So that Steve is entered in the B column 4 times, Tom 7 times, Don 9
times,
etc.
Is this possible ?

Thanks,

Steve

One last thing:
=INDEX(R1:R3,MATCH(RANDBETWEEN(1,SUM(S1:S3)),T1:T3 ))

Here is R, S & T columns.
R S T
Steve 19 1
Tom 17 20
Don 12 37

With the T1 being 1 ( 1)
T2 being 1+19 and (20) S1+T1
T3 being 17 + 20 (37) S2_T2

I guess I'm confusing over the T column. Is Don having the chance to come up
37 times vs. Tom 20 times & Steve only once ?
I wanted to use the #'s in the S colum for the random probability such as
Steve 19, Tom 17 & Don 12. Is the n number of times the S column or the T
column ?

Thanks,

Steve



"T. Valko" wrote:

I should have explained this in my original reply.

In essence the formula works as though each name was repeated n number of
times. From reading your post it seemed that you wanted the list with
repeats just for this single specific purpose. I didn't know whether the
list was used for other things.

Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
Thanks for all your patience. I surely didn't mean to insist that I wanted
that list, I was just adding that to the original random solution that was
provied to me by this group a few years ago. With the Excel 2207 solution
below, it works like a charm, and like you stated, a much cleaner and
improved solution.

Thanks much,

Steve

"T. Valko" wrote:

Improvement:

=INDEX(R1:R3,MATCH(RAND()*SUM(S1:S3)+1,T1:T3))

If you're using Excel 2003 or earlier and have the analysis ToolPak
add-in
installed or, if you're using Excel 2007:

=INDEX(R1:R3,MATCH(RANDBETWEEN(1,SUM(S1:S3)),T1:T3 ))

For any version:

=INDEX(R1:R3,MATCH(ROUND(RAND()*SUM(S1:S3),0)+1,T1 :T3))


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
No need to create the repeated name list.

Try this:

R S
1 Steve 4
2 Tom 7
3 Don 9

In T1 enter 1

Enter this formula in T2 and copy down to T3

=S1+T1

Then:

=INDEX(R1:R3,MATCH(RAND()*SUM(S1:S3)+1,T1:T3))

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
I'm using the following to produce a random picking of names in the B
column.
=INDEX($B$1:$B$100,RAND()*100+1)
Each name is entered a different number of times in the B column, so
that
the names entered the most have a higher probability if hitting.
What I'd like to have is another cells/columns that will auto-enter
the
names in the B column the proper # of times.
E.g. If I have the following:
R S
1 Steve 4
2 Tom 7
3 Don 9

So that Steve is entered in the B column 4 times, Tom 7 times, Don 9
times,
etc.
Is this possible ?

Thanks,

Steve