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.

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

<tbody>
</tbody>

 

[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!