Preference Choice

[haydencohen]

Hi,

I have a list of people who rank their preference of numerous sessions (1st choice, 2nd choice and 3rd Choice) using an MS Form which includes the date and time they made their selection.

Each session has a different room capacity.

I'm wanting a formula that gives the first preference to those who complete the form earlier and as each session fills up, assigning the bottom of the list to their second and third preference.

I've tried using RANK, COUNTIF etc. but can't figure out how to do the preference list.

Does anyone have any ideas?

Kind regards


Hayden

 

[Toadstool]

Hi Haydencohen,

Can you supply the layout of your data and required results? XL2BB would be best but copy & paste as text would show layout and let us avoid typing too much test data.

 

[haydencohen]

Year 1 Optional Module Allocations.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Student No.	First Option:	Second Option:	Third Option	Allocation	Did they get their 1st choice	Completion time	Comments
2		4KNIA041 - Women's Health	4KNIC041 - Childhood in London	4KNIM041 - Mental Health Nursing in Context		Y	9/26/19 14:40
3		4KNIA041 - Women's Health	4KNIM041 - Mental Health Nursing in Context	4KNIA042 - Wellbeing in London		Y	9/26/19 14:41					Modules	Count	Max	Extra
4		4KNIC041 - Childhood in London	4KNIA041 - Women's Health	4KNIM041 - Mental Health Nursing in Context		Y	9/26/19 14:45					4KNIA044 - Global Health Perspectives	0	40	40
5		4KNIA041 - Women's Health	4KNIA043 - Health in London	4KNIA044 - Global Health Perspectives		Y	9/26/19 14:45					4KNIA041 - Women's Health	0	60	60
6		4KNIM041 - Mental Health Nursing in Context	4KNIC041 - Childhood in London	4KNIA041 - Women's Health		Y	9/26/19 14:47					4KNIA042 - Wellbeing in London	0	10	10
7		4KNIA041 - Women's Health	4KNIM041 - Mental Health Nursing in Context	4KNIA042 - Wellbeing in London		Y	9/26/19 14:48					4KNIC041 - Childhood in London	0	80	80
8		4KNIA044 - Global Health Perspectives	4KNIA041 - Women's Health	4KNIA043 - Health in London		Y	9/26/19 14:49					4KNIM041 - Mental Health Nursing in Context	0	50	50
9		4KNIA044 - Global Health Perspectives	4KNIA042 - Wellbeing in London	4KNIA041 - Women's Health		Y	9/26/19 14:50					4KNIA045 - Service in Society	0	10	10
10		4KNIC041 - Childhood in London	4KNIA044 - Global Health Perspectives	4KNIA042 - Wellbeing in London		Y	9/26/19 14:51					4KNIA043 - Health in London	0	30	30
11		4KNIM041 - Mental Health Nursing in Context	4KNIA044 - Global Health Perspectives	4KNIA041 - Women's Health		Y	9/26/19 14:56
12		4KNIM041 - Mental Health Nursing in Context	4KNIA044 - Global Health Perspectives	4KNIA042 - Wellbeing in London		Y	9/26/19 15:05:45
13		4KNIA043 - Health in London	4KNIA044 - Global Health Perspectives	4KNIA045 - Service in London		Y	9/26/19 15:06
Allocations

Cell Formulas
Range	Formula
M4:M10	M4	=COUNTIF(Allocations!$E:$E,L4)
O4:O10	O4	=N4-M4

 

[haydencohen]

Thanks Toadstool,

By way of explanation, the system records the date and time of form completion, the 'did they get their 1st choice' column is required as if the answer is no, they are prioritised (but I also won't need this for first years).

The other table to the right hand side list the modules and the maximum number of students allowed on each course. Once the number is full, it should revert to the student's second choice.

Does that make sense?