Project allocation

I have a spreadsheet with 130 projects in columns and 110 students in
rows. Each student has submitted a list of 5 projects they would like
to do. Is there a computational method of assigning each student one of
their 5 projects, while minimising the number of students that cannot
be offered one of their choices? Is there a method which allows a
manual over-ride of the computational solution to give some students a
preference, where this has been indicated?

Thanks

John

John,

In Excel, you can do this with a lot of manual operations, but with formulas
to help you make the decisions. (My wife and I do registration for school
activities, which is very similar to your task.)

In column A, put your students names (5 times each). In column B, put 1 to
5 repeated, for each student. In column C, put their prefered project #s.
In column D, leave a spot to put Yes or No as the students are assigned the
projects. In column E, use a formula like

=SUMPRODUCT(($B$2:$B$600=B2)*($C$2:$C$600=C2)*($D$2:$D$600=""))

and in column F, use a formula like

=SUMPRODUCT(($C$2:$C$600=C2)*($D$2:$D$600=""))

and copy down to match your data set.

In the end, you will have a list like

Student Number Project Yes/No Priority/Count
OverallCount
John T. 1 #123 1
10
John T. 2 #97 3
21
John T. 3 #65 2
1
John T. 4 #15 4
13
John T. 5 #43 1
15
Bernie D. 1 #97
2 21
Bernie D. 2 #88
1 1
Bernie D. 3 #66
4 14
Bernie D. 4 #59
6 3
Bernie D. 5 #111
1 2

Then you can apply filters to show different things : all the kids who have
chosen project 97 (filter on column C), all the kids who have chosen unique
projects (filter for 1 on column F), all the uniquely chosen projects of
high priority (filter for 1 on column E).

Then when you assign a project, put a Yes in the column next to the kid who
gets it, then refilter to show only the project, and put No in all the other
rows where kids have selected it. Then refilter to show the kid who got the
project, and put a no next to allhis other possible projects. Then start
over again.....

In the end, you'll have to ask some unlucky kids to rechoose their projects,
unless you allow duplications, which you don't appear to do.

HTH,
Bernie
MS Excel MVP




<johntippins@mac.com> wrote in message
news:1111404661.713451.282350@g14g2000cwa.googlegroups.com...
>I have a spreadsheet with 130 projects in columns and 110 students in
> rows. Each student has submitted a list of 5 projects they would like
> to do. Is there a computational method of assigning each student one of
> their 5 projects, while minimising the number of students that cannot
> be offered one of their choices? Is there a method which allows a
> manual over-ride of the computational solution to give some students a
> preference, where this has been indicated?
>
> Thanks
>
> John
>

John,

You should be able to use the Solver Add-in to do this. First, install
the Solver Add-in if you haven't. In one part of your spreadsheet, you
should have the students desires with all of the data filled in; let's
say you it put it on the bottom. At the top, you should have the
assigned status, which will be blank for now. To the right of your top
table, you should have one column which has the count of the students
assigned to one project (which should be either zero or one, if I
understand correctly). For the bottom table, you should also add one
column, which notes if a student does not have any of their desires
fulfilled. You would need one cell which totals the number that are
not fulfilled which would be the target for Solver to minimize. You
would set constraints using Solver that each top table additional cell
should equal one or zero. You would set Solver to change the cells in
the top table.