Formula to enter names in a column specific # of times

[Steve]

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

 

[T. Valko]

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))

 

[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]

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

http://tinyurl.com/2hajwo

--
Biff
Microsoft Excel MVP

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

No need to create the repeated name list.

Try this:


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))

 

[T. Valko]

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))

 

[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]

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!

 

[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]

This is how it works...

........ R.........S.....T
1..Steve.....19.....1
2..Tom......17.....20
3..Don......12.....37

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

SUM(S1:S3) = 48 so:

RANDBETWEEN(1,SUM(S1:S3)) returns a random number from 1 to 48

Let's assume that the random number is 27.

Using the MATCH function and omitting the the match_type argument which then
defaults to match_type 1 meaning the lookup_array is in ascending (which is
what I have done), if an exact match of the lookup_value (our random number)
is not found it will match the greatest value that is less than the
lookup_value.

Using our random number of 27 and the lookup_array in T1:T3 (1;20;37) there
is no exact match so the greatest value that is less than 27 is 20 which is
in position 2. So:

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

This is how it breaks out for each of the names:

Random numbers from 1 to 19 = Steve
Random numbers from 20 to 36 = Tom
Random numbers from 37 to 48 = Don

So:

Random numbers from 1 to 19 means Steve can be selected up 19 times
Random numbers from 20 to 36 means Tom can be selected up to 17 times
Random numbers from 37 to 48 means Don can be selected up to 12 times

Like I stated in my other reply, the formula is working as though the names
have been repeated n number of times. This is actually pretty slick if think
you about it! <g>

It's like these folks have bought raffle tickets and they're being picked
out of a hat. Steve has 19 tickets, Tom has 17 tickets and Don has 12
tickets. So Steve has the best chance of being the winner.

Hope that makes sense.

 

[Steve]

Again, thank you for your patience.

And yes, it's very slick, and exactly what I was trying to achieve. I
couldn't follow some of the technical explanation, but the bottom line
explanation using raffle tix made it very easy to understand. In case you're
interested, the random process was to get a free agent draft order each week
in our Fantasy Football league based on various criterea such as overall
won-loss percent, points for, and # of prior free agent aquisions. Your help
in getting the random generator to work as I hoped has been a perfect and
fair solution.

Thanks again for all your patience, creativity and skill.

Steve

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Again, thank you for your patience.

And yes, it's very slick, and exactly what I was trying to achieve. I
couldn't follow some of the technical explanation, but the bottom line
explanation using raffle tix made it very easy to understand. In case
you're
interested, the random process was to get a free agent draft order each
week
in our Fantasy Football league based on various criterea such as overall
won-loss percent, points for, and # of prior free agent aquisions. Your
help
in getting the random generator to work as I hoped has been a perfect and
fair solution.

Thanks again for all your patience, creativity and skill.

Steve