Assigning people to places based on rank and preference
 

Hi, can someone suggest how to do this please?

I have a number of students that go on work placement each sememster. It's
competitive as to which workplace they are assigned, so we've told them that
they will have priority based on their results from the previous semester
(GPA).

They list the workplaces they'd like to go in order of preference and then I
try and work out who goes where and make it as equitable as possible. The
number of workplaces that take students changes regularly.

So if anyone could please suggest how I:
Rank them on GPA, assign them to the workplace & am able to alter the
workplace numbers without pulling out my hair, it'd be really appreciated.

Cheers!

Assigning people to places based on rank and preference
 

It's hard to pin down a strategy without more information. Roughly how
many students and how many workplaces? Do some workplaces have room for
more than one student? If so, do you know ahead of time?

I would start with a table, perhaps Student names in column A,
Workplace names in row 1, and the numbers 1, 2, 3... somewhere in each
row of the table to designate the student's preferences, possibly not
all Workplaces being ranked.

Probably put GPA in Column B, and formula (to be determined) in C. Might
have to add another row indicating # of positions available per workplace.

A start?

- David

Tongsy wrote:
Hi, can someone suggest how to do this please?

I have a number of students that go on work placement each sememster. It's
competitive as to which workplace they are assigned, so we've told them that
they will have priority based on their results from the previous semester
(GPA).

They list the workplaces they'd like to go in order of preference and then I
try and work out who goes where and make it as equitable as possible. The
number of workplaces that take students changes regularly.

So if anyone could please suggest how I:
Rank them on GPA, assign them to the workplace & am able to alter the
workplace numbers without pulling out my hair, it'd be really appreciated.

Cheers!

Assigning people to places based on rank and preference
 

Thanks David,
I might have anywhere between 15 and 40 students in each group. We have 14
workplaces and they accept anywhere between 1 and 8 of these students. The
final number of students that each centre takes can change up to the week in
advance.

You're right, they won't always preference all of the centres - they visit 4
during the degree and they can't go to the same one twice.

I'm setting up the file as you've described now.

Thanks again

"David Hilberg" wrote:

It's hard to pin down a strategy without more information. Roughly how
many students and how many workplaces? Do some workplaces have room for
more than one student? If so, do you know ahead of time?

I would start with a table, perhaps Student names in column A,
Workplace names in row 1, and the numbers 1, 2, 3... somewhere in each
row of the table to designate the student's preferences, possibly not
all Workplaces being ranked.

Probably put GPA in Column B, and formula (to be determined) in C. Might
have to add another row indicating # of positions available per workplace.

A start?

- David

Tongsy wrote:
Hi, can someone suggest how to do this please?

I have a number of students that go on work placement each sememster. It's
competitive as to which workplace they are assigned, so we've told them that
they will have priority based on their results from the previous semester
(GPA).

They list the workplaces they'd like to go in order of preference and then I
try and work out who goes where and make it as equitable as possible. The
number of workplaces that take students changes regularly.

So if anyone could please suggest how I:
Rank them on GPA, assign them to the workplace & am able to alter the
workplace numbers without pulling out my hair, it'd be really appreciated.

Cheers!

Assigning people to places based on rank and preference
 

Tongsy - I have an elaborate formula for you, but first, what is your
policy or practice if all of a student's choices are pre-empted by those
with higher GPAs?

- David

Tongsy wrote:
Thanks David,
I might have anywhere between 15 and 40 students in each group. We have 14
workplaces and they accept anywhere between 1 and 8 of these students. The
final number of students that each centre takes can change up to the week in
advance.

You're right, they won't always preference all of the centres - they visit 4
during the degree and they can't go to the same one twice.

I'm setting up the file as you've described now.

Thanks again

"David Hilberg" wrote:

It's hard to pin down a strategy without more information. Roughly how
many students and how many workplaces? Do some workplaces have room for
more than one student? If so, do you know ahead of time?

I would start with a table, perhaps Student names in column A,
Workplace names in row 1, and the numbers 1, 2, 3... somewhere in each
row of the table to designate the student's preferences, possibly not
all Workplaces being ranked.

Probably put GPA in Column B, and formula (to be determined) in C. Might
have to add another row indicating # of positions available per workplace.

A start?

- David

Tongsy wrote:
Hi, can someone suggest how to do this please?

I have a number of students that go on work placement each sememster. It's
competitive as to which workplace they are assigned, so we've told them that
they will have priority based on their results from the previous semester
(GPA).

They list the workplaces they'd like to go in order of preference and then I
try and work out who goes where and make it as equitable as possible. The
number of workplaces that take students changes regularly.

So if anyone could please suggest how I:
Rank them on GPA, assign them to the workplace & am able to alter the
workplace numbers without pulling out my hair, it'd be really appreciated.

Cheers!

Assigning people to places based on rank and preference
 

Thanks David,
In that situation we have negociated with the student such that they are
assigned to a workplace that they haven't preferenced. Sometimes students
agree to swap with others.

I guess we could get around it by having the students assign preferences to
all workplaces?

Cheers,
Tongsy

"David Hilberg" wrote:

Tongsy - I have an elaborate formula for you, but first, what is your
policy or practice if all of a student's choices are pre-empted by those
with higher GPAs?

- David

Tongsy wrote:
Thanks David,
I might have anywhere between 15 and 40 students in each group. We have 14
workplaces and they accept anywhere between 1 and 8 of these students. The
final number of students that each centre takes can change up to the week in
advance.

You're right, they won't always preference all of the centres - they visit 4
during the degree and they can't go to the same one twice.

I'm setting up the file as you've described now.

Thanks again

"David Hilberg" wrote:

It's hard to pin down a strategy without more information. Roughly how
many students and how many workplaces? Do some workplaces have room for
more than one student? If so, do you know ahead of time?

I would start with a table, perhaps Student names in column A,
Workplace names in row 1, and the numbers 1, 2, 3... somewhere in each
row of the table to designate the student's preferences, possibly not
all Workplaces being ranked.

Probably put GPA in Column B, and formula (to be determined) in C. Might
have to add another row indicating # of positions available per workplace.

A start?

- David

Tongsy wrote:
Hi, can someone suggest how to do this please?

I have a number of students that go on work placement each sememster. It's
competitive as to which workplace they are assigned, so we've told them that
they will have priority based on their results from the previous semester
(GPA).

They list the workplaces they'd like to go in order of preference and then I
try and work out who goes where and make it as equitable as possible. The
number of workplaces that take students changes regularly.

So if anyone could please suggest how I:
Rank them on GPA, assign them to the workplace & am able to alter the
workplace numbers without pulling out my hair, it'd be really appreciated.

Cheers!

Assigning people to places based on rank and preference
 

Tongsy,

Either all students could rank all workplaces, or any student left with
no choices could go back and rank the remaining ones. The setup below
probably would not work well if students swapped, however.

I've inserted some rows and columns:

Rows 1-2 ... Blank/Your titles
Row 3 ... Workplace Numbering for the formula (0, 1, 2, 3, ... 14, 15 )
Row 4 ... Workplace Names
Row 5 ... Total positions per Workplace

The first Workplace, 0, in column E, is a dummy workplace, assigned if a
student is out of options. It is number 0, it may be called "Nothing
4U," and it must have 0 Total Positions.

It is a good idea if the last workplace is a dummy, too, to ease
insertion/deletion of workplaces. It would be number 15 (or whatever),
it could be called "Dummy" and would have 0 Total positions. I have
assumed its existence in the formula below.

Columns, with titles in row 6:
A ... GPA (slightly easier to sort on GPA if in column A)
B ... Student name
C ... WP# as assigned by a formula
D ... Corresponding WP Name as given by a formula

- Student info should begin in row 7.
- After inputting all student info, it is necessary to sort the student
rows based on GPA, with best at top.
- Next, select the table of rankings (from the first Dummy column to the
last and from row 7 to the last student row); use the Replace command
(Ctrl + H) to replace blanks with 999. This way a blank cell can never
outrank a filled one.

In C7 enter:

=MATCH( MIN((E7:T7-999) * ((PosCt-TRANSPOSE( COUNTIF( $C$6:C6,
-1+ROW(INDIRECT("1:"&WPcols)))))0)), (E7:T7-999) * ((PosCt-TRANSPOSE(
COUNTIF($C$6:C6, -1+ROW(INDIRECT("1:"&WPcols)))))0), 0) -1

- Don't worry.. at this stage you're supposed to see an error.
- Either select and name the "Total Position" cells in E5:T5 as PosCt,
or substitute $E$5:$T$5 for "PosCt" both places in the formula.
- You may substitute 16 for WPcols both places in the formula. WPcols is
a name for a cell which maintains a count of the Workplace columns. Here
there would be 16 (from 0 to 15). In cell U4 I have the formula
=COUNTA(E4:T4), named WPcols, which self-adjusts when Workplaces are
added or deleted.
- The big formula is an array formula, so after editing, enter it with
Ctrl + Shift + Enter. If you're successful, Excel will bracket it with {
}. If not successful, you'll see #VALUE! and will have to pretend to
re-edit (F2), and Ctrl + Shift + Enter again.
- Once successfully entered, the formula can be copied down for all
students.

In D7 enter:

=HLOOKUP(C7,$E$3:$T$5,2)
and copy down. This formula gives you the corresponding Workplace name.


Notes:

- If a Workplace informs you of a last-minute change in Total Positions
available to your students, you can make the appropriate change in row
5. There may be a cascade of re-assignments.
- A student with no available Workplaces will receive 0 / "Nothing
4U" (or whatever you call the first dummy column). More rankings can be
manually added to his row until an available Workplace appears.
- If you insert or delete Workplace columns, you'll have to renumber the
Row 3 numbers from 0 ... whatever.
- It is safe to re-sort the student rows if GPAs change. The placements
will change automatically.
- There is no indicator to show the number of positions remaining in
each Workplace. Another row could be added for that.

I think this should keep you busy for a while. Goodnight for now, and
good luck...

- David






I've added a few rows and columns to my test setup
Tongsy wrote:
Thanks David,
In that situation we have negociated with the student such that they are
assigned to a workplace that they haven't preferenced. Sometimes students
agree to swap with others.

I guess we could get around it by having the students assign preferences to
all workplaces?

Cheers,
Tongsy

"David Hilberg" wrote:

Tongsy - I have an elaborate formula for you, but first, what is your
policy or practice if all of a student's choices are pre-empted by those
with higher GPAs?

- David

Tongsy wrote:
Thanks David,
I might have anywhere between 15 and 40 students in each group. We have 14
workplaces and they accept anywhere between 1 and 8 of these students. The
final number of students that each centre takes can change up to the week in
advance.

You're right, they won't always preference all of the centres - they visit 4
during the degree and they can't go to the same one twice.

I'm setting up the file as you've described now.

Thanks again

"David Hilberg" wrote:

It's hard to pin down a strategy without more information. Roughly how
many students and how many workplaces? Do some workplaces have room for
more than one student? If so, do you know ahead of time?

I would start with a table, perhaps Student names in column A,
Workplace names in row 1, and the numbers 1, 2, 3... somewhere in each
row of the table to designate the student's preferences, possibly not
all Workplaces being ranked.

Probably put GPA in Column B, and formula (to be determined) in C. Might
have to add another row indicating # of positions available per workplace.

A start?

- David

Tongsy wrote:
Hi, can someone suggest how to do this please?

I have a number of students that go on work placement each sememster. It's
competitive as to which workplace they are assigned, so we've told them that
they will have priority based on their results from the previous semester
(GPA).

They list the workplaces they'd like to go in order of preference and then I
try and work out who goes where and make it as equitable as possible. The
number of workplaces that take students changes regularly.

So if anyone could please suggest how I:
Rank them on GPA, assign them to the workplace & am able to alter the
workplace numbers without pulling out my hair, it'd be really appreciated.

Cheers!

Assigning people to places based on rank and preference
 

David,
I've have to thank you so much for your help - what an amazing amount of
work that you've done for me. I really appreciate it.

I've tried it out and it appears to work really well - I'll have to test it
out a couple of times to make sure I'm using it correctly. Thanks so much!

I'll let you know how I go.

Thanks again,
Tongsy

"David Hilberg" wrote:

Tongsy,

Either all students could rank all workplaces, or any student left with
no choices could go back and rank the remaining ones. The setup below
probably would not work well if students swapped, however.

I've inserted some rows and columns:

Rows 1-2 ... Blank/Your titles
Row 3 ... Workplace Numbering for the formula (0, 1, 2, 3, ... 14, 15 )
Row 4 ... Workplace Names
Row 5 ... Total positions per Workplace

The first Workplace, 0, in column E, is a dummy workplace, assigned if a
student is out of options. It is number 0, it may be called "Nothing
4U," and it must have 0 Total Positions.

It is a good idea if the last workplace is a dummy, too, to ease
insertion/deletion of workplaces. It would be number 15 (or whatever),
it could be called "Dummy" and would have 0 Total positions. I have
assumed its existence in the formula below.

Columns, with titles in row 6:
A ... GPA (slightly easier to sort on GPA if in column A)
B ... Student name
C ... WP# as assigned by a formula
D ... Corresponding WP Name as given by a formula

- Student info should begin in row 7.
- After inputting all student info, it is necessary to sort the student
rows based on GPA, with best at top.
- Next, select the table of rankings (from the first Dummy column to the
last and from row 7 to the last student row); use the Replace command
(Ctrl + H) to replace blanks with 999. This way a blank cell can never
outrank a filled one.

In C7 enter:

=MATCH( MIN((E7:T7-999) * ((PosCt-TRANSPOSE( COUNTIF( $C$6:C6,
-1+ROW(INDIRECT("1:"&WPcols)))))0)), (E7:T7-999) * ((PosCt-TRANSPOSE(
COUNTIF($C$6:C6, -1+ROW(INDIRECT("1:"&WPcols)))))0), 0) -1

- Don't worry.. at this stage you're supposed to see an error.
- Either select and name the "Total Position" cells in E5:T5 as PosCt,
or substitute $E$5:$T$5 for "PosCt" both places in the formula.
- You may substitute 16 for WPcols both places in the formula. WPcols is
a name for a cell which maintains a count of the Workplace columns. Here
there would be 16 (from 0 to 15). In cell U4 I have the formula
=COUNTA(E4:T4), named WPcols, which self-adjusts when Workplaces are
added or deleted.
- The big formula is an array formula, so after editing, enter it with
Ctrl + Shift + Enter. If you're successful, Excel will bracket it with {
}. If not successful, you'll see #VALUE! and will have to pretend to
re-edit (F2), and Ctrl + Shift + Enter again.
- Once successfully entered, the formula can be copied down for all
students.

In D7 enter:

=HLOOKUP(C7,$E$3:$T$5,2)
and copy down. This formula gives you the corresponding Workplace name.


Notes:

- If a Workplace informs you of a last-minute change in Total Positions
available to your students, you can make the appropriate change in row
5. There may be a cascade of re-assignments.
- A student with no available Workplaces will receive 0 / "Nothing
4U" (or whatever you call the first dummy column). More rankings can be
manually added to his row until an available Workplace appears.
- If you insert or delete Workplace columns, you'll have to renumber the
Row 3 numbers from 0 ... whatever.
- It is safe to re-sort the student rows if GPAs change. The placements
will change automatically.
- There is no indicator to show the number of positions remaining in
each Workplace. Another row could be added for that.

I think this should keep you busy for a while. Goodnight for now, and
good luck...

- David






I've added a few rows and columns to my test setup
Tongsy wrote:
Thanks David,
In that situation we have negociated with the student such that they are
assigned to a workplace that they haven't preferenced. Sometimes students
agree to swap with others.

I guess we could get around it by having the students assign preferences to
all workplaces?

Cheers,
Tongsy

"David Hilberg" wrote:

Tongsy - I have an elaborate formula for you, but first, what is your
policy or practice if all of a student's choices are pre-empted by those
with higher GPAs?

- David

Tongsy wrote:
Thanks David,
I might have anywhere between 15 and 40 students in each group. We have 14
workplaces and they accept anywhere between 1 and 8 of these students. The
final number of students that each centre takes can change up to the week in
advance.

You're right, they won't always preference all of the centres - they visit 4
during the degree and they can't go to the same one twice.

I'm setting up the file as you've described now.

Thanks again

"David Hilberg" wrote:

It's hard to pin down a strategy without more information. Roughly how
many students and how many workplaces? Do some workplaces have room for
more than one student? If so, do you know ahead of time?

I would start with a table, perhaps Student names in column A,
Workplace names in row 1, and the numbers 1, 2, 3... somewhere in each
row of the table to designate the student's preferences, possibly not
all Workplaces being ranked.

Probably put GPA in Column B, and formula (to be determined) in C. Might
have to add another row indicating # of positions available per workplace.

A start?

- David

Tongsy wrote:
Hi, can someone suggest how to do this please?

I have a number of students that go on work placement each sememster. It's
competitive as to which workplace they are assigned, so we've told them that
they will have priority based on their results from the previous semester
(GPA).

They list the workplaces they'd like to go in order of preference and then I
try and work out who goes where and make it as equitable as possible. The
number of workplaces that take students changes regularly.

So if anyone could please suggest how I:
Rank them on GPA, assign them to the workplace & am able to alter the
workplace numbers without pulling out my hair, it'd be really appreciated.

Cheers!

Assigning people to places based on rank and preference
 

Tongsy,

Thanks for reporting back -- it means a lot!

Until later,

- David

Tongsy wrote:
David,
I've have to thank you so much for your help - what an amazing amount of
work that you've done for me. I really appreciate it.

I've tried it out and it appears to work really well - I'll have to test it
out a couple of times to make sure I'm using it correctly. Thanks so much!

I'll let you know how I go.

Thanks again,
Tongsy

"David Hilberg" wrote:

Tongsy,

Either all students could rank all workplaces, or any student left with
no choices could go back and rank the remaining ones. The setup below
probably would not work well if students swapped, however.

I've inserted some rows and columns:

Rows 1-2 ... Blank/Your titles
Row 3 ... Workplace Numbering for the formula (0, 1, 2, 3, ... 14, 15 )
Row 4 ... Workplace Names
Row 5 ... Total positions per Workplace

The first Workplace, 0, in column E, is a dummy workplace, assigned if a
student is out of options. It is number 0, it may be called "Nothing
4U," and it must have 0 Total Positions.

It is a good idea if the last workplace is a dummy, too, to ease
insertion/deletion of workplaces. It would be number 15 (or whatever),
it could be called "Dummy" and would have 0 Total positions. I have
assumed its existence in the formula below.

Columns, with titles in row 6:
A ... GPA (slightly easier to sort on GPA if in column A)
B ... Student name
C ... WP# as assigned by a formula
D ... Corresponding WP Name as given by a formula

- Student info should begin in row 7.
- After inputting all student info, it is necessary to sort the student
rows based on GPA, with best at top.
- Next, select the table of rankings (from the first Dummy column to the
last and from row 7 to the last student row); use the Replace command
(Ctrl + H) to replace blanks with 999. This way a blank cell can never
outrank a filled one.

In C7 enter:

=MATCH( MIN((E7:T7-999) * ((PosCt-TRANSPOSE( COUNTIF( $C$6:C6,
-1+ROW(INDIRECT("1:"&WPcols)))))0)), (E7:T7-999) * ((PosCt-TRANSPOSE(
COUNTIF($C$6:C6, -1+ROW(INDIRECT("1:"&WPcols)))))0), 0) -1

- Don't worry.. at this stage you're supposed to see an error.
- Either select and name the "Total Position" cells in E5:T5 as PosCt,
or substitute $E$5:$T$5 for "PosCt" both places in the formula.
- You may substitute 16 for WPcols both places in the formula. WPcols is
a name for a cell which maintains a count of the Workplace columns. Here
there would be 16 (from 0 to 15). In cell U4 I have the formula
=COUNTA(E4:T4), named WPcols, which self-adjusts when Workplaces are
added or deleted.
- The big formula is an array formula, so after editing, enter it with
Ctrl + Shift + Enter. If you're successful, Excel will bracket it with {
}. If not successful, you'll see #VALUE! and will have to pretend to
re-edit (F2), and Ctrl + Shift + Enter again.
- Once successfully entered, the formula can be copied down for all
students.

In D7 enter:

=HLOOKUP(C7,$E$3:$T$5,2)
and copy down. This formula gives you the corresponding Workplace name.


Notes:

- If a Workplace informs you of a last-minute change in Total Positions
available to your students, you can make the appropriate change in row
5. There may be a cascade of re-assignments.
- A student with no available Workplaces will receive 0 / "Nothing
4U" (or whatever you call the first dummy column). More rankings can be
manually added to his row until an available Workplace appears.
- If you insert or delete Workplace columns, you'll have to renumber the
Row 3 numbers from 0 ... whatever.
- It is safe to re-sort the student rows if GPAs change. The placements
will change automatically.
- There is no indicator to show the number of positions remaining in
each Workplace. Another row could be added for that.

I think this should keep you busy for a while. Goodnight for now, and
good luck...

- David






I've added a few rows and columns to my test setup
Tongsy wrote:
Thanks David,
In that situation we have negociated with the student such that they are
assigned to a workplace that they haven't preferenced. Sometimes students
agree to swap with others.

I guess we could get around it by having the students assign preferences to
all workplaces?

Cheers,
Tongsy

"David Hilberg" wrote:

Tongsy - I have an elaborate formula for you, but first, what is your
policy or practice if all of a student's choices are pre-empted by those
with higher GPAs?

- David

Tongsy wrote:
Thanks David,
I might have anywhere between 15 and 40 students in each group. We have 14
workplaces and they accept anywhere between 1 and 8 of these students. The
final number of students that each centre takes can change up to the week in
advance.

You're right, they won't always preference all of the centres - they visit 4
during the degree and they can't go to the same one twice.

I'm setting up the file as you've described now.

Thanks again

"David Hilberg" wrote:

It's hard to pin down a strategy without more information. Roughly how
many students and how many workplaces? Do some workplaces have room for
more than one student? If so, do you know ahead of time?

I would start with a table, perhaps Student names in column A,
Workplace names in row 1, and the numbers 1, 2, 3... somewhere in each
row of the table to designate the student's preferences, possibly not
all Workplaces being ranked.

Probably put GPA in Column B, and formula (to be determined) in C. Might
have to add another row indicating # of positions available per workplace.

A start?

- David

Tongsy wrote:
Hi, can someone suggest how to do this please?

I have a number of students that go on work placement each sememster. It's
competitive as to which workplace they are assigned, so we've told them that
they will have priority based on their results from the previous semester
(GPA).

They list the workplaces they'd like to go in order of preference and then I
try and work out who goes where and make it as equitable as possible. The
number of workplaces that take students changes regularly.

So if anyone could please suggest how I:
Rank them on GPA, assign them to the workplace & am able to alter the
workplace numbers without pulling out my hair, it'd be really appreciated.

Cheers!