Placement into program based on score and choice

[mlore]

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]

 

[Snakehips]

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
	A	B	C	D	E	F	G	H	I	J	K	L
1	Score	First Name:	Middle Name:	Last Name:	1st Choice:	2nd Choice:	3rd Choice:		Allocated		Program	Max
2	57.4	First 1	Middle 1	Last 1	SPB	ENG	IT		SPB		AT	4
3	55.93	First 2	Middle 2	Last 2	ENG	LE	SPB		ENG		CON	4
4	55.78	First 3	Middle 3	Last 3	HSM	ENG	IT		HSM		COS	4
5	55.38	First 4	Middle 4	Last 4	IT	SPB	CUL		IT		CUL	4
6	55.1	First 5	Middle 5	Last 5	ENG	HSM	LE		ENG		ENG	8
7	55	First 6	Middle 6	Last 6	ENG	IT	AT		ENG		HSM	8
8	54.98	First 7	Middle 7	Last 7	LE	IT	HSM		LE		IT	4
9	54.88	First 8	Middle 8	Last 8	HSM	HSM	HSM		HSM		LE	4
10	54.85	First 9	Middle 9	Last 9	HSM	WEL	SPB		HSM		SPB	4
11	54.85	First 10	Middle 10	Last 10	SPB	ENG	HSM		SPB		WEL	4
12	54.8	First 11	Middle 11	Last 11	IT	ENG	SPB		IT
13	54.43	First 12	Middle 12	Last 12	HSM	ENG	LE		HSM
14	53.9	First 13	Middle 13	Last 13	HSM	HSM	HSM		HSM
15	53.8	First 14	Middle 14	Last 14	IT	ENG	SPB		IT
16	53.8	First 15	Middle 15	Last 15	SPB	CUL	IT		SPB
17	53.68	First 16	Middle 16	Last 16	HSM	ENG	IT		HSM
18	53.33	First 17	Middle 17	Last 17	ENG	IT	CON		ENG
19	53.13	First 18	Middle 18	Last 18	HSM	ENG	SPB		HSM
20	53.03	First 19	Middle 19	Last 19	IT	ENG	HSM		IT
21	53	First 20	Middle 20	Last 20	ENG	IT	AT		ENG
22	53	First 21	Middle 21	Last 21	ENG	LE	HSM		ENG
23	52.73	First 22	Middle 22	Last 22	HSM	LE	CUL		HSM
24	52.55	First 23	Middle 23	Last 23	ENG	IT	HSM		ENG
25	52.53	First 24	Middle 24	Last 24	ENG	IT	LE		ENG
26	52.3	First 25	Middle 25	Last 25	HSM	SPB	COS		SPB
27	52.2	First 26	Middle 26	Last 26	ENG	SPB	IT
28	51.88	First 27	Middle 27	Last 27	LE	ENG	IT		LE
Sheet7

Cell Formulas
Range	Formula
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.

 

[mlore]

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?

 

[mlore]

Never mind it was user error It works perfect!