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