auto populate

jdog · 10-15-2005, 01:05 PM

I am trying to figure out how to take a list of names and fill a time slot
list randomly with those names. each name can only be used up to 2 times.
its around 14-16 names with 25 -35 slots.

Any suggestions

R. Choate · 10-15-2005, 01:05 PM

Sounds like a homework problem. Are you trying to solve this with VBA or with formulas? How far have you gotten with it?

--
RMC,CPA

"jdog" <jdog@discussions.microsoft.com> wrote in message news:E06B75BC-B057-4280-B64D-8EE8FE8255F9@microsoft.com...
I am trying to figure out how to take a list of names and fill a time slot
list randomly with those names. each name can only be used up to 2 times.
its around 14-16 names with 25 -35 slots.

Any suggestions

**Bryan Hessey** · 10-15-2005, 09:42 PM

I agree that it sounds like homework / assignment, but possible is:

Assuming that your list of names is in B1 to B16, and that column A is free.

insert
=rand()
in a1 to a32 (ie, twice the name range 14 to 16)

then use

=INDEX(B$1:B$16,IF(MATCH(SMALL(A$1:A$32,ROW()),A$1:A$32,0)<17,MATCH(SMALL(A$1:A$32,ROW()),A$1:A$32,0),MATCH(SMALL(A$1:A$32,ROW()),A$1:A$32,0)-16))

- explained as Index (select) b1:b16 depending on:
use the smallest of A1 to A32 (minus 16 if applicable) according to the current row number (thus the 5th row selects the 5th smallest)

This does not ensure that all names are used at least once.
It also is random and does not prevent a name being selected consecutively.

Hope this helps.

Originally Posted by R. Choate

Sounds like a homework problem. Are you trying to solve this with VBA or with formulas? How far have you gotten with it?

--
RMC,CPA

"jdog" <jdog@discussions.microsoft.com> wrote in message news:E06B75BC-B057-4280-B64D-8EE8FE8255F9@microsoft.com...
I am trying to figure out how to take a list of names and fill a time slot
list randomly with those names. each name can only be used up to 2 times.
its around 14-16 names with 25 -35 slots.

Any suggestions

B. R.Ramachandran · 10-16-2005, 01:05 AM

Hi,

If you have 16 names and more than 32 time slots, some names WILL have to be
used more than twice. Let us imagine that you have 32 time slots and 16
names.

Place the time-slot-list in A1:A32 and the name-list in B1:B16.
Copy B1:B16 and paste onto B17:B32.
Create a helper column C1:C32 with random numbers. For this, in C1
enter
=RAND() and fill-down the formula to C32.
Copy the range C1:C32, "Edit"-->"Paste Special"/"Values" onto C1:C12
itself.
Now select B1:C32 (i.e., the name and random number columns; do not
select column A) and sort by column C.

Regards,
B. R. Ramachandran

"jdog" wrote:

> I am trying to figure out how to take a list of names and fill a time slot
> list randomly with those names. each name can only be used up to 2 times.
> its around 14-16 names with 25 -35 slots.
>
> Any suggestions

Bernd Plumhoff · 10-21-2005, 07:05 PM

Hello,

I suggest to take my UDF UniqRandInt() from www.sulprobil.com.

If your names are listed in A1:A16, for example, then select cells B1:B25
and enter
=INDEX(A1:A16,UniqRandInt(16,2))
as array-formula (with CTRL+SHIFT+ENTER).

Please keep in mind that my function returns an error value if the length of
your list is less than 16 * 2 as B. R. Ramachandran already pointed out.

HTH,
Bernd