Placement into program based on score and choice

M

mlore

New Member
Joined
Oct 31, 2017
Messages
3
Can anyone help with a formula or code that would automatically place individuals into their 1st, 2nd, or 3rd choice of program based on the following:
  • The individual with the highest score determines the priority of placement
  • There are a maximum number of placements for each program
  • Placement should be done in rounds. All individuals are placed in their first choice based on their score until the programs are full. Anyone who did not get into their 1st choice will be placed into their 2nd choice based on score again. If their score is higher than someone who was placed in the first round they can bump and then that individual who was bumped 2nd choice will need to be evaluated. This process will occur a third time for those who have not been placed 3rd choice.

Please see the sample data below which is sorted by score and spans columns A-G and rows 1-40.

[TABLE="width: 942"]
<tbody>[TR]
[TD]Program[/TD]
[TD]Max[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AT[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CON[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]COS[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CUL[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ENG[/TD]
[TD]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HSM[/TD]
[TD]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LE[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SPB[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WEL[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]IT[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Score[/TD]
[TD]First Name:[/TD]
[TD]Middle Name:[/TD]
[TD]Last Name:[/TD]
[TD]1st Choice:[/TD]
[TD]2nd Choice:[/TD]
[TD]3rd Choice:[/TD]
[/TR]
[TR]
[TD]57.40[/TD]
[TD]First 1[/TD]
[TD]Middle 1[/TD]
[TD]Last 1[/TD]
[TD]SPB[/TD]
[TD]ENG[/TD]
[TD]IT[/TD]
[/TR]
[TR]
[TD]55.93[/TD]
[TD]First 2[/TD]
[TD]Middle 2[/TD]
[TD]Last 2[/TD]
[TD]ENG[/TD]
[TD]LE[/TD]
[TD]SPB[/TD]
[/TR]
[TR]
[TD]55.78[/TD]
[TD]First 3[/TD]
[TD]Middle 3[/TD]
[TD]Last 3[/TD]
[TD]HSM[/TD]
[TD]ENG[/TD]
[TD]IT[/TD]
[/TR]
[TR]
[TD]55.38[/TD]
[TD]First 4[/TD]
[TD]Middle 4[/TD]
[TD]Last 4[/TD]
[TD]IT[/TD]
[TD]SPB[/TD]
[TD]CUL[/TD]
[/TR]
[TR]
[TD]55.10[/TD]
[TD]First 5[/TD]
[TD]Middle 5[/TD]
[TD]Last 5[/TD]
[TD]ENG[/TD]
[TD]HSM[/TD]
[TD]LE[/TD]
[/TR]
[TR]
[TD]55.00[/TD]
[TD]First 6[/TD]
[TD]Middle 6[/TD]
[TD]Last 6[/TD]
[TD]ENG[/TD]
[TD]IT[/TD]
[TD]AT[/TD]
[/TR]
[TR]
[TD]54.98[/TD]
[TD]First 7[/TD]
[TD]Middle 7[/TD]
[TD]Last 7[/TD]
[TD]LE[/TD]
[TD]IT[/TD]
[TD]HSM[/TD]
[/TR]
[TR]
[TD]54.88[/TD]
[TD]First 8[/TD]
[TD]Middle 8[/TD]
[TD]Last 8[/TD]
[TD]HSM[/TD]
[TD]HSM[/TD]
[TD]HSM[/TD]
[/TR]
[TR]
[TD]54.85[/TD]
[TD]First 9[/TD]
[TD]Middle 9[/TD]
[TD]Last 9[/TD]
[TD]HSM[/TD]
[TD]WEL[/TD]
[TD]SPB[/TD]
[/TR]
[TR]
[TD]54.85[/TD]
[TD]First 10[/TD]
[TD]Middle 10[/TD]
[TD]Last 10[/TD]
[TD]SPB[/TD]
[TD]ENG[/TD]
[TD]HSM[/TD]
[/TR]
[TR]
[TD]54.80[/TD]
[TD]First 11[/TD]
[TD]Middle 11[/TD]
[TD]Last 11[/TD]
[TD]IT[/TD]
[TD]ENG[/TD]
[TD]SPB[/TD]
[/TR]
[TR]
[TD]54.43[/TD]
[TD]First 12[/TD]
[TD]Middle 12[/TD]
[TD]Last 12[/TD]
[TD]HSM[/TD]
[TD]ENG[/TD]
[TD]LE[/TD]
[/TR]
[TR]
[TD]53.90[/TD]
[TD]First 13[/TD]
[TD]Middle 13[/TD]
[TD]Last 13[/TD]
[TD]HSM[/TD]
[TD]HSM[/TD]
[TD]HSM[/TD]
[/TR]
[TR]
[TD]53.80[/TD]
[TD]First 14[/TD]
[TD]Middle 14[/TD]
[TD]Last 14[/TD]
[TD]IT[/TD]
[TD]ENG[/TD]
[TD]SPB[/TD]
[/TR]
[TR]
[TD]53.80[/TD]
[TD]First 15[/TD]
[TD]Middle 15[/TD]
[TD]Last 15[/TD]
[TD]SPB[/TD]
[TD]CUL[/TD]
[TD]IT[/TD]
[/TR]
[TR]
[TD]53.68[/TD]
[TD]First 16[/TD]
[TD]Middle 16[/TD]
[TD]Last 16[/TD]
[TD]HSM[/TD]
[TD]ENG[/TD]
[TD]IT[/TD]
[/TR]
[TR]
[TD]53.33[/TD]
[TD]First 17[/TD]
[TD]Middle 17[/TD]
[TD]Last 17[/TD]
[TD]ENG[/TD]
[TD]IT[/TD]
[TD]CON[/TD]
[/TR]
[TR]
[TD]53.13[/TD]
[TD]First 18[/TD]
[TD]Middle 18[/TD]
[TD]Last 18[/TD]
[TD]HSM[/TD]
[TD]ENG[/TD]
[TD]SPB[/TD]
[/TR]
[TR]
[TD]53.03[/TD]
[TD]First 19[/TD]
[TD]Middle 19[/TD]
[TD]Last 19[/TD]
[TD]IT[/TD]
[TD]ENG[/TD]
[TD]HSM[/TD]
[/TR]
[TR]
[TD]53.00[/TD]
[TD]First 20[/TD]
[TD]Middle 20[/TD]
[TD]Last 20[/TD]
[TD]ENG[/TD]
[TD]IT[/TD]
[TD]AT[/TD]
[/TR]
[TR]
[TD]53.00[/TD]
[TD]First 21[/TD]
[TD]Middle 21[/TD]
[TD]Last 21[/TD]
[TD]ENG[/TD]
[TD]LE[/TD]
[TD]HSM[/TD]
[/TR]
[TR]
[TD]52.73[/TD]
[TD]First 22[/TD]
[TD]Middle 22[/TD]
[TD]Last 22[/TD]
[TD]HSM[/TD]
[TD]LE[/TD]
[TD]CUL[/TD]
[/TR]
[TR]
[TD]52.55[/TD]
[TD]First 23[/TD]
[TD]Middle 23[/TD]
[TD]Last 23[/TD]
[TD]ENG[/TD]
[TD]IT[/TD]
[TD]HSM[/TD]
[/TR]
[TR]
[TD]52.53[/TD]
[TD]First 24[/TD]
[TD]Middle 24[/TD]
[TD]Last 24[/TD]
[TD]ENG[/TD]
[TD]IT[/TD]
[TD]LE[/TD]
[/TR]
[TR]
[TD]52.30[/TD]
[TD]First 25[/TD]
[TD]Middle 25[/TD]
[TD]Last 25[/TD]
[TD]HSM[/TD]
[TD]SPB[/TD]
[TD]COS[/TD]
[/TR]
[TR]
[TD]52.20[/TD]
[TD]First 26[/TD]
[TD]Middle 26[/TD]
[TD]Last 26[/TD]
[TD]ENG[/TD]
[TD]SPB[/TD]
[TD]IT[/TD]
[/TR]
[TR]
[TD]51.88[/TD]
[TD]First 27[/TD]
[TD]Middle 27[/TD]
[TD]Last 27[/TD]
[TD]LE[/TD]
[TD]ENG[/TD]
[TD]IT[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
miore,

Subject to editing the the range for the table of available program places or maybe defining it as a named range
AND ensuring that table is sorted in ascending order(otherwise will need INDEX /MATCH rather than VLOOKUP)Try like....

Excel 2010
ABCDEFGHIJKL
1ScoreFirst Name:Middle Name:Last Name:1st Choice:2nd Choice:3rd Choice:AllocatedProgramMax
257.4First 1Middle 1Last 1SPBENGITSPBAT4
355.93First 2Middle 2Last 2ENGLESPBENGCON4
455.78First 3Middle 3Last 3HSMENGITHSMCOS4
555.38First 4Middle 4Last 4ITSPBCULITCUL4
655.1First 5Middle 5Last 5ENGHSMLEENGENG8
755First 6Middle 6Last 6ENGITATENGHSM8
854.98First 7Middle 7Last 7LEITHSMLEIT4
954.88First 8Middle 8Last 8HSMHSMHSMHSMLE4
1054.85First 9Middle 9Last 9HSMWELSPBHSMSPB4
1154.85First 10Middle 10Last 10SPBENGHSMSPBWEL4
1254.8First 11Middle 11Last 11ITENGSPBIT
1354.43First 12Middle 12Last 12HSMENGLEHSM
1453.9First 13Middle 13Last 13HSMHSMHSMHSM
1553.8First 14Middle 14Last 14ITENGSPBIT
1653.8First 15Middle 15Last 15SPBCULITSPB
1753.68First 16Middle 16Last 16HSMENGITHSM
1853.33First 17Middle 17Last 17ENGITCONENG
1953.13First 18Middle 18Last 18HSMENGSPBHSM
2053.03First 19Middle 19Last 19ITENGHSMIT
2153First 20Middle 20Last 20ENGITATENG
2253First 21Middle 21Last 21ENGLEHSMENG
2352.73First 22Middle 22Last 22HSMLECULHSM
2452.55First 23Middle 23Last 23ENGITHSMENG
2552.53First 24Middle 24Last 24ENGITLEENG
2652.3First 25Middle 25Last 25HSMSPBCOSSPB
2752.2First 26Middle 26Last 26ENGSPBIT
2851.88First 27Middle 27Last 27LEENGITLE
Sheet7
Cell Formulas
RangeFormula
I2=IF(COUNTIF($I$1:I1,E2)E2,$K$2:$L$11,2),E2,IF(COUNTIF($I$1:I1,F2)F2,$K$2:$L$11,2),F2,IF(COUNTIF($I$1:I1,G2)G2,$K$2:$L$11,2),G2,"")))


Hope that helps.
 
Upvote 0
Thank you! This is working great...except that my last program in the list is give me results that are double the max. Any idea why?
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Complex tiebreaker
Replies
7
Views
537
HorizonHunter
H
D
  • Question Question
Difficulty with trying to use ComplexSorting subroutine
Replies
0
Views
374
DonEB
D
L
  • Question Question
VBA works on work PC but not home PC
Replies
3
Views
2K
rollis13
rollis13
G
  • Solved
VBA Code to compare single column date on worksheet with 2 column start and end dates, copy select information horizontally, to empty cells.
Replies
4
Views
907
GAdams106
G
W
  • Question Question
Formula/script for a selection procedure
Replies
2
Views
668
WIR053
W

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top