What is the best way to set up a spreadsheet to do this....

[rition]

I have been thinking about a problem for days but still haven't
decided the best way to set up the spreadsheet, last year it was a
logistical nightmare.

I have seventeen trainers who will be able to work one or two sessions
out of nine sessions i.e. Mon-Fri pm, Sat and Sun am/pm and can have a
maximum of five trainees each. Each trainer will only run one training
session per availability i.e. if they are available Mon and Fri they
will only run a course on either Monday or Friday.

Then I have 99 trainees who will be available to attend one or more of
the sessions and I have to allocate as many of them as possible, i.e.

Trainer A can provide training on either Monday pm or Sunday am
Trainer B can provide training on Thursday pm
Trainer C can provide training on Sun pm
etc

Trainee 1 can attend on either Mon or Thur pm
Trainee 2 can only attend on Sunday pm
Trainee 3 can attend on Sunday pm or Monday pm
etc

Can anyone suggest a way to organise this spreadsheet (and, but this
is secondary, then pull the information to a database so that I can
run a letter to the trainer and trainees advising them of their
trainer/trainees and days and dates of their course.)

I know that I haven't made myself very clear so thanks for any help in
advance.

 

[Roger Govier]

Hi

One way that might get you started would be to setup the following,
In B2 enter Mon AM, C2 Tue AM etc. across to Sun PM in J2
In A3 enter TrainerA, then in A8 enter Trainer B going down column A in
increments of 5 until you have all 17 trainers entered.

Repeat headings B2:J2 in M2:U2
In M3 enter name of first student who can attend that session , then the
next and so on, allocating all students to each column of sessions they
can attend.

In cell B3 enter
=INDEX($M$2:$U$101,COUNTIF(B$2:B2,"<>")+1,COLUMN(A1))
copy across through C3:J3, then copy B3:J3 down through B4:B87
This will allocate all of the students to all of the columns.

In M1 enter
=COUNTA(M3:M101) and copy across through N1:U1
This will give a count of the number of students who can attend each
session.

In B1 enter
=COUNTA(B3:B101)&" / "&M1 and copy across through C1:J1
This will show how many students out of the total number have been
allocated to that session

Now, looking at Trainer A, blank out the formulae in the 5 rows of all
of the columns that they cannot attend.

It's a bit hit and miss, but it should get you started

 

[rition]

Thanks Roger, I will give it a go.

 

[rition]

Hi

One way that might get you started would be to setup the following,
In B2 enter Mon AM, C2 Tue AM etc. across to Sun PM in J2
In A3 enter TrainerA, then in A8 enter Trainer B going down column A
in
increments of 5 until you have all 17 trainers entered.

Repeat headings B2:J2 in M2:U2
In M3 enter name of first student who can attend that session , then
the
next and so on, allocating all students to each column of sessions
they
can attend.

In cell B3 enter
=INDEX($M$2:$U$101,COUNTIF(B$2:B2,"<>")+1,COLUMN(A1))
copy across through C3:J3, then copy B3:J3 down through B4:B87
This will allocate all of the students to all of the columns.

In M1 enter
=COUNTA(M3:M101) and copy across through N1:U1
This will give a count of the number of students who can attend each
session.

In B1 enter
=COUNTA(B3:B101)&" / "&M1 and copy across through C1:J1
This will show how many students out of the total number have been
allocated to that session

Now, looking at Trainer A, blank out the formulae in the 5 rows of all
of the columns that they cannot attend.

It's a bit hit and miss, but it should get you started

--
Regards

Roger Govier

Thank you for your help with my excel formula, can I ask one further
question please.

The spreadsheet works fine but it doesn't allocate the names of the
trainees to any rows above the corresponding entry line, i.e. if the
trainee is in N8 even if I have spaces in say C5 it does enter it but
the correct count is displayed in C1.

I have looked at the formula but cannot see what instruction to change
to correct this.

Is it possible to alter this?

Thanks again.

 

[Roger Govier]

Hi

Apologies for the delay in response. Some fool chopped through our cable
and I lost all telephone and internet contact for over 12 hours.

I can't quite see what you are saying.
If you have not removed the formula from C5, then it should pick up the
extra trainee.
If you want to mail a copy of your workbook direct to me, I will take a
look and see if I can see what is going wrong.
To mail direct, remove NOSAPM from my address.

 

[rition]

Hi

Apologies for the delay in response. Some fool chopped through our cable
and I lost all telephone and internet contact for over 12 hours.

I can't quite see what you are saying.
If you have not removed the formula from C5, then it should pick up the
extra trainee.
If you want to mail a copy of your workbook direct to me, I will take a
look and see if I can see what is going wrong.
To mail direct, remove NOSAPM from my address.

Thank you Roger, I have sent you a copy.

Wendy

 

[Roger Govier]

Hi Wendy

Copy received modified and returned.
The problem was leaving gaps between the list of trainees in each of the
9 columns.
Each column should contain a contiguous list of names, no gaps.
If you want to give trainees preference in order of receiving training,
then list them in the columns in a priority order, or on a first come
first served basis