Example: I have 1,036 nominees and each one has to be interviewed by 3
different people out of a group of 20 interviewers.

1,036/20 = 51.8 nominees per interviewer
1,036-(51*20)=16
So 16 people would have 52 nominees and 4 would have 51 nominees.
(!6*52)+4*51=1,036
And I can figure all this out ahead of time.

So I use INT(RAND()*20) in 3 columns and 1036 rows.

Of course some rows will have duplicates but I can, in another 3 columns
look at the rows with duplicates and assign another random number to
replace the duplicate, no big deal. In my test case I did this two
times and afterward only had 3 rows left with duplicates.

The problem is that INT(RAND()*20) has no idea that it can only have 51
zeros, 51 ones, 51 twos, etc or 52 instead of the 51, I can manually
adjust for the 4 51s and 16 52s.

In my test case here is what happened
165 zeros
158 ones
190 twos
150 threes
167 fours
142 fives
157 sixes
166 sevens
159 eights
147 etc
170
154
160
141
152
154
154
135
143
147
-----
3111 total random numbers assigned, I only wanted 1,036.

Not cool.

So I have to find something better or someway to limit my command with
another command while at the same time leaving the randomness.

Any ideas?

There are 560 unique combinations of 20 people taken 3 at a time.

I would generate those combinations and then make a copy for a total of
1120.

Then I would assign the list of 1036 to these.

Put the 20 names in B1 to U1

Then in row 1 to 1121, put a 1 below each person that would interview a the
candiate in that row.

then to get a list for each interviewer, you could use an autofilter.

This sounds like a scheduling nightmare, however.

It seems like you would probably want to organize 6 teams with two floaters,
or 5 teams of 4 with one person being designated as a backup for each team.


Also, your initial analysis is off. You need to conduct 3*1036 interviews,
so each person would need to do

(3*1036)/20 = 155.4 interviews, not 52/51

I can generate the 560 combinations if you need it.

--
Regards,
Tom Ogilvy


"Dick Hassler" wrote in message
...
Example: I have 1,036 nominees and each one has to be interviewed by 3
different people out of a group of 20 interviewers.

1,036/20 = 51.8 nominees per interviewer
1,036-(51*20)=16
So 16 people would have 52 nominees and 4 would have 51 nominees.
(!6*52)+4*51=1,036
And I can figure all this out ahead of time.

So I use INT(RAND()*20) in 3 columns and 1036 rows.

Of course some rows will have duplicates but I can, in another 3 columns
look at the rows with duplicates and assign another random number to
replace the duplicate, no big deal. In my test case I did this two
times and afterward only had 3 rows left with duplicates.

The problem is that INT(RAND()*20) has no idea that it can only have 51
zeros, 51 ones, 51 twos, etc or 52 instead of the 51, I can manually
adjust for the 4 51s and 16 52s.

In my test case here is what happened
165 zeros
158 ones
190 twos
150 threes
167 fours
142 fives
157 sixes
166 sevens
159 eights
147 etc
170
154
160
141
152
154
154
135
143
147
-----
3111 total random numbers assigned, I only wanted 1,036.

Not cool.

So I have to find something better or someway to limit my command with
another command while at the same time leaving the randomness.

Any ideas?

Dick,
Hang in there. I'm still thinking about this puppy, as no doubt at least
a few others are.

Dave Braden

In article
,
Dick Hassler wrote:

Example: I have 1,036 nominees and each one has to be interviewed by 3
different people out of a group of 20 interviewers.

1,036/20 = 51.8 nominees per interviewer
1,036-(51*20)=16
So 16 people would have 52 nominees and 4 would have 51 nominees.
(!6*52)+4*51=1,036
And I can figure all this out ahead of time.

So I use INT(RAND()*20) in 3 columns and 1036 rows.

Of course some rows will have duplicates but I can, in another 3 columns
look at the rows with duplicates and assign another random number to
replace the duplicate, no big deal. In my test case I did this two
times and afterward only had 3 rows left with duplicates.

The problem is that INT(RAND()*20) has no idea that it can only have 51
zeros, 51 ones, 51 twos, etc or 52 instead of the 51, I can manually
adjust for the 4 51s and 16 52s.

In my test case here is what happened
165 zeros
158 ones
190 twos
150 threes
167 fours
142 fives
157 sixes
166 sevens
159 eights
147 etc
170
154
160
141
152
154
154
135
143
147
-----
3111 total random numbers assigned, I only wanted 1,036.

Not cool.

So I have to find something better or someway to limit my command with
another command while at the same time leaving the randomness.

Any ideas?

--
(ROT13)

"Dick Hassler" wrote...
Example: I have 1,036 nominees and each one has to be interviewed by 3
different people out of a group of 20 interviewers.

1,036/20 = 51.8 nominees per interviewer

No, every interviewer would have 3 times this many nominees if each nominee
needs to be interviewed by 3 different interviewers.

If you want as many different interviewer groups as possible, then create a
list of of all groups of 3 interviewers out of 20 interviewers. There are
more than enough of these since COMBIN(20,3) = 1140 1036. In a 3 col by
1140 row range (I'll use A1:C1140) enter A, B and C in each of the top row's
cells. Then enter these formulas in the second row.

A2:
=IF(B1<"S",A1,CHAR(CODE(A1)+1))

B2:
=IF(C1<"T",B1,CHAR(CODE(B1)+1))

C2:
=CHAR(CODE(IF(C1<"T",C1,B2))+1)

Select A2:C2 and fill down into A3:C1140. Then enter =RAND() in each cell in
D1:D1140. Sort A1:D1140 on column D (ascending or descending order doesn't
matter). Take the top 1036 of these. Assign one set of 3 interviewers to
each nominee. Scheduling these is a whole different kettle of fish.

"Tom Ogilvy" wrote...
There are 560 unique combinations of 20 people taken 3 at a time.

Last time I checked, COMBIN(20,3) was 1140 rather than 560.

"Harlan Grove" wrote...
....
Select A2:C2 and fill down into A3:C1140. . . .

Forgot a step. At this point you need to select A1:C1140, copy, and paste
special as values on top of itself. The the combinations won't be fubarred
when sorted.

yep, combin(16,3) = 560

Guess I made a mistake on the number of interviewers and the OP's analysis.

--
Regards,
Tom Ogilvy


Harlan Grove wrote in message
...
"Tom Ogilvy" wrote...
There are 560 unique combinations of 20 people taken 3 at a time.

Last time I checked, COMBIN(20,3) was 1140 rather than 560.

Why sort anyway?

--
Regards,
Tom Ogilvy

Harlan Grove wrote in message
...
"Harlan Grove" wrote...
...
Select A2:C2 and fill down into A3:C1140. . . .

Forgot a step. At this point you need to select A1:C1140, copy, and paste
special as values on top of itself. The the combinations won't be fubarred
when sorted.

"Tom Ogilvy" wrote...
Why sort anyway?
....

You're right - no need as long as the assignment of letters to interviewers
is arbitrary. Adding one shuffle of either interviewers or nominees won't
hurt, and it does ensure the assignment of interviewers to nominees isn't
purely a function of their respective list orders.