Help creating model to allocate preferred choices but with limited no. of allocations per choice to maximize ranked preferences

[sjf92]

Windows 365
I have a 334 students. I have 17 pathways (choices) with a limited number of spaces per pathway. Students are able to select 5 pathways and rank them in terms of preference (1 being most preferred and 5 being the least). I would like to enter the preference data into a model/formula in Excel that will then tell me the allocation of topics to students that will maximize the preferences of the greatest number of students.
If there is an existing tutorial or thread that provides an answer, I'd be grateful for any leads. I have only the most basic proficiency in Excel and thus will need a simple step-by-step explanation of how to build this model.

Pathway	Available Spaces
ACC	22​
BE	20​
CF	18​
CVS	20​
Endo	32​
GAH	21​
GH	25​
HPL	18​
II	20​
M	40​
NEU	32​
HAEM	15​
PH	30​
RM	16​
RDS	30​
SDT	20​
TRM	25​

 

[kevin9999]

One possible way...
You'll need to set your sheet out exactly as I've indicated below. Note that the Pathway list has been sorted by available spaces (largest to smallest) and that your student list starts in cell D2 - with headers in row 1. I've randomly picked the students' preferences to test the code. Put the code below in a standard module. I've shown the sheet before and after the code is run.
With the 334 randomly selected preferences, it results in 308 getting their 1st preference; 21 getting their second; 4 their 3rd and 1 their 4th. There may be a more equitable way of doing this, but this is what I came up with.

The code:

Option Explicit
Sub Allocate_Pathways()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Set ws = Sheets("Sheet1")   '<-- *** Change sheet name to suit ***
    Dim r As Range, LRow As Long
    Set r = ws.Range("D2:K" & ws.Cells(Rows.Count, "D").End(xlUp).Row)
    LRow = r.Columns(1).Cells.Count
    
    Dim a
    a = ws.Range("A2", ws.Cells(Rows.Count, "B").End(xlUp))
    Dim s As String, i As Long, j As Long, k As Long, n As Long
    For k = 2 To 6
        For i = 1 To UBound(a, 1)
            s = a(i, 1)
            n = a(i, 2) - WorksheetFunction.CountIf(r.Columns(7), s)
            For j = 1 To LRow 'r.Columns(1).Cells.Count
                If n > 0 And r.Cells(j, k) = s And r.Cells(j, 7) = "" Then
                    r.Cells(j, 7) = s
                    n = n - 1
                End If
            Next j
        Next i
    Next k
    
    ws.Range("J1").Resize(1, 2).Value = Array("Pathway", "Preference")
    With r.Columns(8)
        .FormulaR1C1 = "=Match(RC[-1],RC[-6]:RC[-2],0)"
        .Value = .Value
    End With
    Application.ScreenUpdating = False
End Sub

Before (only the first 300 students shown due to XL2BB limits):

TEST ALLOCATION.xlsm
	A	B	C	D	E	F	G	H	I
1	Pathway	Available spaces		Student	1st	2nd	3rd	4th	5th
2	M	40		Student 1	RM	Endo	CVS	GH	II
3	Endo	32		Student 2	NEU	HPL	BE	M	PH
4	NEU	32		Student 3	GAH	CVS	CF	BE	PH
5	PH	30		Student 4	HPL	TRM	II	BE	RDS
6	RDS	30		Student 5	GAH	II	HPL	ACC	GH
7	GH	25		Student 6	GAH	II	PH	Endo	M
8	TRM	25		Student 7	ACC	GH	BE	M	TRM
9	ACC	22		Student 8	NEU	CVS	BE	II	HPL
10	GAH	21		Student 9	CVS	ACC	TRM	CF	GH
11	BE	20		Student 10	HPL	RM	TRM	M	GH
12	CVS	20		Student 11	SDT	M	GAH	HAEM	ACC
13	II	20		Student 12	II	HPL	CF	Endo	NEU
14	SDT	20		Student 13	TRM	PH	Endo	SDT	GAH
15	CF	18		Student 14	Endo	II	M	HAEM	HPL
16	HPL	18		Student 15	CVS	RDS	GH	GAH	ACC
17	RM	16		Student 16	TRM	HPL	GAH	II	M
18	HAEM	15		Student 17	BE	PH	SDT	HPL	NEU
19				Student 18	HAEM	PH	CF	ACC	GH
20				Student 19	HPL	Endo	GAH	ACC	NEU
21				Student 20	HAEM	PH	BE	M	NEU
22				Student 21	NEU	HAEM	HPL	PH	GAH
23				Student 22	NEU	ACC	CF	CVS	M
24				Student 23	BE	HPL	Endo	RM	GAH
25				Student 24	SDT	CF	PH	M	GH
26				Student 25	TRM	CVS	GH	NEU	BE
27				Student 26	ACC	RM	HPL	II	M
28				Student 27	GAH	NEU	GH	ACC	HPL
29				Student 28	TRM	GAH	HAEM	Endo	M
30				Student 29	TRM	CF	Endo	M	RM
31				Student 30	II	GAH	GH	RM	NEU
32				Student 31	RM	CF	TRM	RDS	CVS
33				Student 32	PH	ACC	CVS	RM	SDT
34				Student 33	Endo	GH	GAH	CF	HPL
35				Student 34	NEU	CVS	GAH	PH	RDS
36				Student 35	ACC	PH	GH	II	BE
37				Student 36	ACC	II	TRM	PH	BE
38				Student 37	BE	CF	II	Endo	HPL
39				Student 38	GH	RM	GAH	II	RDS
40				Student 39	BE	GH	M	PH	CF
41				Student 40	NEU	ACC	SDT	CVS	RM
42				Student 41	GH	HPL	TRM	Endo	RM
43				Student 42	GH	RM	M	CVS	NEU
44				Student 43	CF	GH	BE	Endo	PH
45				Student 44	NEU	GAH	TRM	M	II
46				Student 45	PH	RM	HPL	BE	ACC
47				Student 46	CF	SDT	TRM	NEU	GH
48				Student 47	BE	GH	M	PH	NEU
49				Student 48	BE	HAEM	M	SDT	CVS
50				Student 49	II	HPL	CF	CVS	NEU
51				Student 50	NEU	ACC	II	HPL	SDT
52				Student 51	BE	HPL	RDS	HAEM	Endo
53				Student 52	CF	NEU	HAEM	TRM	PH
54				Student 53	CF	GAH	SDT	NEU	BE
55				Student 54	SDT	II	HPL	TRM	M
56				Student 55	Endo	BE	GAH	CF	RM
57				Student 56	BE	GH	TRM	M	CF
58				Student 57	II	CF	Endo	M	RM
59				Student 58	CF	NEU	RM	ACC	M
60				Student 59	RDS	CVS	GH	PH	II
61				Student 60	HAEM	M	SDT	GH	II
62				Student 61	ACC	CVS	GAH	PH	RM
63				Student 62	NEU	HAEM	HPL	SDT	M
64				Student 63	CF	M	GH	RM	Endo
65				Student 64	II	GAH	RDS	PH	M
66				Student 65	RDS	II	HAEM	SDT	NEU
67				Student 66	RDS	PH	ACC	RM	TRM
68				Student 67	GH	GAH	HPL	RDS	RM
69				Student 68	HAEM	PH	GAH	CF	NEU
70				Student 69	NEU	BE	CVS	M	CF
71				Student 70	GAH	CVS	II	RDS	ACC
72				Student 71	BE	CF	II	HPL	RDS
73				Student 72	ACC	TRM	CVS	HAEM	SDT
74				Student 73	ACC	M	BE	II	SDT
75				Student 74	TRM	ACC	PH	Endo	II
76				Student 75	RDS	TRM	RM	SDT	HAEM
77				Student 76	BE	TRM	CVS	PH	GAH
78				Student 77	HAEM	HPL	RM	CF	II
79				Student 78	ACC	HPL	GAH	NEU	RDS
80				Student 79	II	HAEM	PH	CF	TRM
81				Student 80	ACC	GAH	CF	TRM	CVS
82				Student 81	BE	M	CVS	PH	CF
83				Student 82	HAEM	SDT	CF	PH	GAH
84				Student 83	GH	SDT	II	CF	M
85				Student 84	RM	NEU	M	II	CF
86				Student 85	PH	M	ACC	NEU	II
87				Student 86	GAH	GH	SDT	NEU	CVS
88				Student 87	PH	HPL	CVS	HAEM	ACC
89				Student 88	SDT	CF	M	NEU	II
90				Student 89	CF	TRM	SDT	NEU	M
91				Student 90	CVS	SDT	RM	M	TRM
92				Student 91	RM	SDT	PH	Endo	M
93				Student 92	RM	ACC	GAH	NEU	II
94				Student 93	PH	RM	NEU	M	II
95				Student 94	M	GAH	ACC	NEU	II
96				Student 95	CF	HAEM	TRM	GH	BE
97				Student 96	BE	NEU	GAH	RM	II
98				Student 97	RDS	SDT	PH	RM	HPL
99				Student 98	ACC	II	HAEM	BE	CVS
100				Student 99	II	GAH	PH	TRM	RM
101				Student 100	RDS	SDT	TRM	GH	M
102				Student 101	M	CVS	RM	NEU	II
103				Student 102	Endo	GH	SDT	RDS	TRM
104				Student 103	II	BE	CVS	CF	TRM
105				Student 104	Endo	TRM	BE	CVS	M
106				Student 105	Endo	HPL	BE	RDS	II
107				Student 106	CF	HPL	BE	SDT	M
108				Student 107	GAH	RDS	SDT	BE	RM
109				Student 108	NEU	II	HPL	SDT	ACC
110				Student 109	GH	HPL	Endo	NEU	HAEM
111				Student 110	Endo	PH	HAEM	ACC	M
112				Student 111	SDT	M	HPL	GAH	RDS
113				Student 112	TRM	HPL	NEU	M	RM
114				Student 113	RDS	PH	NEU	GAH	RM
115				Student 114	II	GH	M	TRM	RDS
116				Student 115	TRM	NEU	II	GAH	GH
117				Student 116	ACC	CVS	GH	Endo	GAH
118				Student 117	GAH	TRM	RM	CVS	SDT
119				Student 118	PH	TRM	M	NEU	GAH
120				Student 119	II	RDS	RM	CF	ACC
121				Student 120	NEU	BE	RDS	GH	PH
122				Student 121	CF	II	HAEM	GAH	CVS
123				Student 122	NEU	RDS	M	GH	GAH
124				Student 123	GAH	SDT	CF	II	RDS
125				Student 124	ACC	HAEM	PH	RM	CVS
126				Student 125	PH	ACC	RM	SDT	HAEM
127				Student 126	HAEM	M	BE	RM	RDS
128				Student 127	RDS	NEU	SDT	II	GAH
129				Student 128	CVS	PH	NEU	CF	HPL
130				Student 129	GAH	CF	M	RDS	CVS
131				Student 130	GAH	BE	Endo	CVS	RDS
132				Student 131	ACC	II	GH	TRM	BE
133				Student 132	NEU	GH	RDS	II	ACC
134				Student 133	NEU	RM	ACC	SDT	II
135				Student 134	GAH	SDT	ACC	GH	CF
136				Student 135	II	HPL	CVS	RM	RDS
137				Student 136	II	RDS	HPL	M	HAEM
138				Student 137	TRM	ACC	HPL	M	Endo
139				Student 138	II	HPL	CVS	RDS	NEU
140				Student 139	HAEM	PH	Endo	SDT	M
141				Student 140	NEU	II	HPL	SDT	M
142				Student 141	CF	GAH	BE	SDT	Endo
143				Student 142	GH	CVS	HPL	CF	SDT
144				Student 143	II	CVS	GH	SDT	HPL
145				Student 144	CVS	ACC	M	II	SDT
146				Student 145	ACC	GAH	CF	HPL	Endo
147				Student 146	TRM	ACC	HAEM	M	SDT
148				Student 147	BE	GH	Endo	M	NEU
149				Student 148	PH	TRM	II	Endo	NEU
150				Student 149	GH	ACC	SDT	RDS	CVS
151				Student 150	NEU	PH	Endo	M	CVS
152				Student 151	RDS	CVS	II	ACC	RM
153				Student 152	ACC	RM	HPL	PH	GH
154				Student 153	CF	HAEM	M	PH	II
155				Student 154	Endo	GAH	SDT	II	HAEM
156				Student 155	GAH	M	TRM	CVS	HAEM
157				Student 156	GAH	PH	HPL	GH	II
158				Student 157	BE	TRM	HPL	SDT	GAH
159				Student 158	CVS	II	ACC	M	GH
160				Student 159	GAH	TRM	ACC	SDT	RDS
161				Student 160	BE	CVS	Endo	ACC	RM
162				Student 161	II	HAEM	RM	BE	Endo
163				Student 162	II	ACC	M	Endo	HPL
164				Student 163	Endo	GAH	PH	RM	II
165				Student 164	HPL	II	Endo	RM	TRM
166				Student 165	RDS	NEU	TRM	CF	II
167				Student 166	RDS	NEU	GH	CF	TRM
168				Student 167	TRM	RDS	HPL	SDT	RM
169				Student 168	RDS	HPL	CVS	II	NEU
170				Student 169	M	GH	HPL	HAEM	II
171				Student 170	NEU	GAH	II	HAEM	CVS
172				Student 171	GAH	Endo	RDS	HAEM	SDT
173				Student 172	CF	GAH	ACC	II	HPL
174				Student 173	HPL	RM	HAEM	M	II
175				Student 174	GAH	M	BE	GH	SDT
176				Student 175	TRM	GH	CF	RM	PH
177				Student 176	II	M	ACC	NEU	RDS
178				Student 177	II	CF	TRM	HPL	GH
179				Student 178	RDS	TRM	HAEM	II	PH
180				Student 179	II	CVS	HAEM	M	Endo
181				Student 180	M	HPL	PH	TRM	GAH
182				Student 181	PH	CVS	GAH	ACC	NEU
183				Student 182	RM	M	BE	GH	II
184				Student 183	PH	CF	RDS	RM	Endo
185				Student 184	NEU	II	BE	GAH	M
186				Student 185	HPL	RDS	NEU	PH	Endo
187				Student 186	II	GAH	SDT	ACC	CF
188				Student 187	CF	Endo	RM	TRM	NEU
189				Student 188	HPL	GAH	CVS	Endo	NEU
190				Student 189	M	GAH	SDT	NEU	ACC
191				Student 190	RDS	PH	M	II	BE
192				Student 191	M	TRM	HAEM	II	SDT
193				Student 192	HPL	II	GAH	NEU	TRM
194				Student 193	HPL	NEU	Endo	CVS	ACC
195				Student 194	BE	TRM	ACC	M	Endo
196				Student 195	NEU	RM	ACC	GAH	GH
197				Student 196	CVS	HAEM	GAH	BE	GH
198				Student 197	ACC	CVS	GH	TRM	RM
199				Student 198	HAEM	GH	SDT	NEU	RDS
200				Student 199	TRM	HPL	NEU	M	GAH
201				Student 200	CVS	II	CF	Endo	M
202				Student 201	HAEM	CF	II	GAH	BE
203				Student 202	II	HPL	GH	Endo	GAH
204				Student 203	HPL	TRM	II	RM	ACC
205				Student 204	HAEM	CF	HPL	ACC	GAH
206				Student 205	SDT	HPL	GH	GAH	M
207				Student 206	SDT	CVS	CF	GH	Endo
208				Student 207	HAEM	GH	PH	RDS	M
209				Student 208	PH	BE	M	Endo	RM
210				Student 209	RM	CF	SDT	CVS	ACC
211				Student 210	RM	GAH	CF	PH	RDS
212				Student 211	M	HAEM	TRM	GAH	NEU
213				Student 212	CF	PH	RDS	NEU	HPL
214				Student 213	ACC	NEU	GH	II	CF
215				Student 214	BE	SDT	CF	HPL	TRM
216				Student 215	II	GH	Endo	M	NEU
217				Student 216	TRM	SDT	BE	RM	M
218				Student 217	PH	RM	HAEM	NEU	SDT
219				Student 218	II	ACC	M	GAH	TRM
220				Student 219	ACC	GH	HPL	M	PH
221				Student 220	CVS	Endo	ACC	RM	SDT
222				Student 221	NEU	HPL	RM	RDS	HAEM
223				Student 222	PH	ACC	M	II	SDT
224				Student 223	SDT	HAEM	CVS	HPL	PH
225				Student 224	RM	CVS	M	II	GAH
226				Student 225	CVS	ACC	HPL	SDT	Endo
227				Student 226	Endo	PH	II	RM	BE
228				Student 227	TRM	NEU	RDS	CVS	RM
229				Student 228	CF	SDT	M	II	ACC
230				Student 229	RM	M	CVS	RDS	NEU
231				Student 230	CF	PH	GH	SDT	NEU
232				Student 231	RM	SDT	TRM	M	II
233				Student 232	CVS	CF	NEU	ACC	SDT
234				Student 233	GH	BE	M	ACC	SDT
235				Student 234	CVS	HAEM	M	NEU	RDS
236				Student 235	GH	CVS	M	CF	II
237				Student 236	Endo	RDS	CVS	M	NEU
238				Student 237	M	PH	GAH	II	HAEM
239				Student 238	CF	II	PH	M	GH
240				Student 239	CF	SDT	M	RM	Endo
241				Student 240	M	NEU	SDT	TRM	RDS
242				Student 241	RM	RDS	NEU	II	SDT
243				Student 242	GH	ACC	CVS	RM	SDT
244				Student 243	HAEM	NEU	Endo	II	M
245				Student 244	GH	TRM	RM	M	CVS
246				Student 245	CVS	CF	SDT	HAEM	ACC
247				Student 246	RDS	NEU	M	SDT	TRM
248				Student 247	II	HAEM	TRM	M	Endo
249				Student 248	CF	SDT	NEU	Endo	GAH
250				Student 249	ACC	HPL	RDS	GH	II
251				Student 250	HPL	Endo	CF	HAEM	SDT
252				Student 251	ACC	TRM	HAEM	CF	GAH
253				Student 252	TRM	ACC	M	II	RM
254				Student 253	RDS	CF	RM	GH	HAEM
255				Student 254	TRM	BE	SDT	NEU	GAH
256				Student 255	TRM	RDS	ACC	NEU	Endo
257				Student 256	HAEM	HPL	M	BE	GH
258				Student 257	HAEM	TRM	HPL	RM	CF
259				Student 258	GH	PH	HAEM	NEU	M
260				Student 259	SDT	PH	GH	M	BE
261				Student 260	CVS	II	GH	M	NEU
262				Student 261	NEU	BE	SDT	GAH	RM
263				Student 262	Endo	NEU	CVS	RM	HAEM
264				Student 263	M	GAH	NEU	GH	RM
265				Student 264	BE	RM	TRM	CVS	SDT
266				Student 265	BE	CF	NEU	RDS	HAEM
267				Student 266	HPL	BE	PH	ACC	II
268				Student 267	HAEM	M	BE	NEU	GAH
269				Student 268	RDS	NEU	TRM	PH	CF
270				Student 269	GAH	NEU	CF	RDS	RM
271				Student 270	TRM	CF	HPL	CVS	M
272				Student 271	PH	II	ACC	HPL	RM
273				Student 272	CVS	M	RM	ACC	GAH
274				Student 273	PH	Endo	GAH	II	GH
275				Student 274	Endo	PH	GH	BE	ACC
276				Student 275	CF	M	HAEM	ACC	PH
277				Student 276	HPL	M	TRM	GH	SDT
278				Student 277	ACC	CF	HAEM	PH	GAH
279				Student 278	RM	GH	CF	RDS	M
280				Student 279	HAEM	PH	M	NEU	II
281				Student 280	HAEM	TRM	M	BE	ACC
282				Student 281	RDS	GAH	ACC	PH	RM
283				Student 282	ACC	RM	BE	GH	M
284				Student 283	II	M	SDT	CVS	BE
285				Student 284	CF	NEU	Endo	GH	HPL
286				Student 285	GAH	BE	PH	TRM	CVS
287				Student 286	II	SDT	PH	ACC	M
288				Student 287	M	HAEM	SDT	CF	TRM
289				Student 288	TRM	M	II	GH	NEU
290				Student 289	M	BE	NEU	PH	SDT
291				Student 290	HPL	II	M	RDS	TRM
292				Student 291	PH	BE	CF	II	SDT
293				Student 292	ACC	M	GAH	TRM	RM
294				Student 293	RDS	II	GH	NEU	ACC
295				Student 294	BE	II	HPL	CVS	Endo
296				Student 295	RM	M	PH	NEU	II
297				Student 296	NEU	HPL	II	M	PH
298				Student 297	NEU	II	RM	PH	GH
299				Student 298	TRM	M	HPL	NEU	BE
300				Student 299	M	II	CF	GH	HAEM
301				Student 300	NEU	TRM	HPL	M	CVS
Sheet1

After (only the first 250 students shown due to XL2BB limits):

TEST ALLOCATION.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Pathway	Available spaces		Student	1st	2nd	3rd	4th	5th	Pathway	Preference
2	M	40		Student 1	RM	Endo	CVS	GH	II	RM	1
3	Endo	32		Student 2	NEU	HPL	BE	M	PH	NEU	1
4	NEU	32		Student 3	GAH	CVS	CF	BE	PH	GAH	1
5	PH	30		Student 4	HPL	TRM	II	BE	RDS	HPL	1
6	RDS	30		Student 5	GAH	II	HPL	ACC	GH	GAH	1
7	GH	25		Student 6	GAH	II	PH	Endo	M	GAH	1
8	TRM	25		Student 7	ACC	GH	BE	M	TRM	ACC	1
9	ACC	22		Student 8	NEU	CVS	BE	II	HPL	NEU	1
10	GAH	21		Student 9	CVS	ACC	TRM	CF	GH	CVS	1
11	BE	20		Student 10	HPL	RM	TRM	M	GH	HPL	1
12	CVS	20		Student 11	SDT	M	GAH	HAEM	ACC	SDT	1
13	II	20		Student 12	II	HPL	CF	Endo	NEU	II	1
14	SDT	20		Student 13	TRM	PH	Endo	SDT	GAH	TRM	1
15	CF	18		Student 14	Endo	II	M	HAEM	HPL	Endo	1
16	HPL	18		Student 15	CVS	RDS	GH	GAH	ACC	CVS	1
17	RM	16		Student 16	TRM	HPL	GAH	II	M	TRM	1
18	HAEM	15		Student 17	BE	PH	SDT	HPL	NEU	BE	1
19				Student 18	HAEM	PH	CF	ACC	GH	HAEM	1
20				Student 19	HPL	Endo	GAH	ACC	NEU	HPL	1
21				Student 20	HAEM	PH	BE	M	NEU	HAEM	1
22				Student 21	NEU	HAEM	HPL	PH	GAH	NEU	1
23				Student 22	NEU	ACC	CF	CVS	M	NEU	1
24				Student 23	BE	HPL	Endo	RM	GAH	BE	1
25				Student 24	SDT	CF	PH	M	GH	SDT	1
26				Student 25	TRM	CVS	GH	NEU	BE	TRM	1
27				Student 26	ACC	RM	HPL	II	M	ACC	1
28				Student 27	GAH	NEU	GH	ACC	HPL	GAH	1
29				Student 28	TRM	GAH	HAEM	Endo	M	TRM	1
30				Student 29	TRM	CF	Endo	M	RM	TRM	1
31				Student 30	II	GAH	GH	RM	NEU	II	1
32				Student 31	RM	CF	TRM	RDS	CVS	RM	1
33				Student 32	PH	ACC	CVS	RM	SDT	PH	1
34				Student 33	Endo	GH	GAH	CF	HPL	Endo	1
35				Student 34	NEU	CVS	GAH	PH	RDS	NEU	1
36				Student 35	ACC	PH	GH	II	BE	ACC	1
37				Student 36	ACC	II	TRM	PH	BE	ACC	1
38				Student 37	BE	CF	II	Endo	HPL	BE	1
39				Student 38	GH	RM	GAH	II	RDS	GH	1
40				Student 39	BE	GH	M	PH	CF	BE	1
41				Student 40	NEU	ACC	SDT	CVS	RM	NEU	1
42				Student 41	GH	HPL	TRM	Endo	RM	GH	1
43				Student 42	GH	RM	M	CVS	NEU	GH	1
44				Student 43	CF	GH	BE	Endo	PH	CF	1
45				Student 44	NEU	GAH	TRM	M	II	NEU	1
46				Student 45	PH	RM	HPL	BE	ACC	PH	1
47				Student 46	CF	SDT	TRM	NEU	GH	CF	1
48				Student 47	BE	GH	M	PH	NEU	BE	1
49				Student 48	BE	HAEM	M	SDT	CVS	BE	1
50				Student 49	II	HPL	CF	CVS	NEU	II	1
51				Student 50	NEU	ACC	II	HPL	SDT	NEU	1
52				Student 51	BE	HPL	RDS	HAEM	Endo	BE	1
53				Student 52	CF	NEU	HAEM	TRM	PH	CF	1
54				Student 53	CF	GAH	SDT	NEU	BE	CF	1
55				Student 54	SDT	II	HPL	TRM	M	SDT	1
56				Student 55	Endo	BE	GAH	CF	RM	Endo	1
57				Student 56	BE	GH	TRM	M	CF	BE	1
58				Student 57	II	CF	Endo	M	RM	II	1
59				Student 58	CF	NEU	RM	ACC	M	CF	1
60				Student 59	RDS	CVS	GH	PH	II	RDS	1
61				Student 60	HAEM	M	SDT	GH	II	HAEM	1
62				Student 61	ACC	CVS	GAH	PH	RM	ACC	1
63				Student 62	NEU	HAEM	HPL	SDT	M	NEU	1
64				Student 63	CF	M	GH	RM	Endo	CF	1
65				Student 64	II	GAH	RDS	PH	M	II	1
66				Student 65	RDS	II	HAEM	SDT	NEU	RDS	1
67				Student 66	RDS	PH	ACC	RM	TRM	RDS	1
68				Student 67	GH	GAH	HPL	RDS	RM	GH	1
69				Student 68	HAEM	PH	GAH	CF	NEU	HAEM	1
70				Student 69	NEU	BE	CVS	M	CF	NEU	1
71				Student 70	GAH	CVS	II	RDS	ACC	GAH	1
72				Student 71	BE	CF	II	HPL	RDS	BE	1
73				Student 72	ACC	TRM	CVS	HAEM	SDT	ACC	1
74				Student 73	ACC	M	BE	II	SDT	ACC	1
75				Student 74	TRM	ACC	PH	Endo	II	TRM	1
76				Student 75	RDS	TRM	RM	SDT	HAEM	RDS	1
77				Student 76	BE	TRM	CVS	PH	GAH	BE	1
78				Student 77	HAEM	HPL	RM	CF	II	HAEM	1
79				Student 78	ACC	HPL	GAH	NEU	RDS	ACC	1
80				Student 79	II	HAEM	PH	CF	TRM	II	1
81				Student 80	ACC	GAH	CF	TRM	CVS	ACC	1
82				Student 81	BE	M	CVS	PH	CF	BE	1
83				Student 82	HAEM	SDT	CF	PH	GAH	HAEM	1
84				Student 83	GH	SDT	II	CF	M	GH	1
85				Student 84	RM	NEU	M	II	CF	RM	1
86				Student 85	PH	M	ACC	NEU	II	PH	1
87				Student 86	GAH	GH	SDT	NEU	CVS	GAH	1
88				Student 87	PH	HPL	CVS	HAEM	ACC	PH	1
89				Student 88	SDT	CF	M	NEU	II	SDT	1
90				Student 89	CF	TRM	SDT	NEU	M	CF	1
91				Student 90	CVS	SDT	RM	M	TRM	CVS	1
92				Student 91	RM	SDT	PH	Endo	M	RM	1
93				Student 92	RM	ACC	GAH	NEU	II	RM	1
94				Student 93	PH	RM	NEU	M	II	PH	1
95				Student 94	M	GAH	ACC	NEU	II	M	1
96				Student 95	CF	HAEM	TRM	GH	BE	CF	1
97				Student 96	BE	NEU	GAH	RM	II	BE	1
98				Student 97	RDS	SDT	PH	RM	HPL	RDS	1
99				Student 98	ACC	II	HAEM	BE	CVS	ACC	1
100				Student 99	II	GAH	PH	TRM	RM	II	1
101				Student 100	RDS	SDT	TRM	GH	M	RDS	1
102				Student 101	M	CVS	RM	NEU	II	M	1
103				Student 102	Endo	GH	SDT	RDS	TRM	Endo	1
104				Student 103	II	BE	CVS	CF	TRM	II	1
105				Student 104	Endo	TRM	BE	CVS	M	Endo	1
106				Student 105	Endo	HPL	BE	RDS	II	Endo	1
107				Student 106	CF	HPL	BE	SDT	M	CF	1
108				Student 107	GAH	RDS	SDT	BE	RM	GAH	1
109				Student 108	NEU	II	HPL	SDT	ACC	NEU	1
110				Student 109	GH	HPL	Endo	NEU	HAEM	GH	1
111				Student 110	Endo	PH	HAEM	ACC	M	Endo	1
112				Student 111	SDT	M	HPL	GAH	RDS	SDT	1
113				Student 112	TRM	HPL	NEU	M	RM	TRM	1
114				Student 113	RDS	PH	NEU	GAH	RM	RDS	1
115				Student 114	II	GH	M	TRM	RDS	II	1
116				Student 115	TRM	NEU	II	GAH	GH	TRM	1
117				Student 116	ACC	CVS	GH	Endo	GAH	ACC	1
118				Student 117	GAH	TRM	RM	CVS	SDT	GAH	1
119				Student 118	PH	TRM	M	NEU	GAH	PH	1
120				Student 119	II	RDS	RM	CF	ACC	II	1
121				Student 120	NEU	BE	RDS	GH	PH	NEU	1
122				Student 121	CF	II	HAEM	GAH	CVS	CF	1
123				Student 122	NEU	RDS	M	GH	GAH	NEU	1
124				Student 123	GAH	SDT	CF	II	RDS	GAH	1
125				Student 124	ACC	HAEM	PH	RM	CVS	ACC	1
126				Student 125	PH	ACC	RM	SDT	HAEM	PH	1
127				Student 126	HAEM	M	BE	RM	RDS	HAEM	1
128				Student 127	RDS	NEU	SDT	II	GAH	RDS	1
129				Student 128	CVS	PH	NEU	CF	HPL	CVS	1
130				Student 129	GAH	CF	M	RDS	CVS	GAH	1
131				Student 130	GAH	BE	Endo	CVS	RDS	GAH	1
132				Student 131	ACC	II	GH	TRM	BE	ACC	1
133				Student 132	NEU	GH	RDS	II	ACC	NEU	1
134				Student 133	NEU	RM	ACC	SDT	II	NEU	1
135				Student 134	GAH	SDT	ACC	GH	CF	GAH	1
136				Student 135	II	HPL	CVS	RM	RDS	II	1
137				Student 136	II	RDS	HPL	M	HAEM	II	1
138				Student 137	TRM	ACC	HPL	M	Endo	TRM	1
139				Student 138	II	HPL	CVS	RDS	NEU	II	1
140				Student 139	HAEM	PH	Endo	SDT	M	HAEM	1
141				Student 140	NEU	II	HPL	SDT	M	NEU	1
142				Student 141	CF	GAH	BE	SDT	Endo	CF	1
143				Student 142	GH	CVS	HPL	CF	SDT	GH	1
144				Student 143	II	CVS	GH	SDT	HPL	II	1
145				Student 144	CVS	ACC	M	II	SDT	CVS	1
146				Student 145	ACC	GAH	CF	HPL	Endo	ACC	1
147				Student 146	TRM	ACC	HAEM	M	SDT	TRM	1
148				Student 147	BE	GH	Endo	M	NEU	BE	1
149				Student 148	PH	TRM	II	Endo	NEU	PH	1
150				Student 149	GH	ACC	SDT	RDS	CVS	GH	1
151				Student 150	NEU	PH	Endo	M	CVS	NEU	1
152				Student 151	RDS	CVS	II	ACC	RM	RDS	1
153				Student 152	ACC	RM	HPL	PH	GH	ACC	1
154				Student 153	CF	HAEM	M	PH	II	CF	1
155				Student 154	Endo	GAH	SDT	II	HAEM	Endo	1
156				Student 155	GAH	M	TRM	CVS	HAEM	GAH	1
157				Student 156	GAH	PH	HPL	GH	II	GAH	1
158				Student 157	BE	TRM	HPL	SDT	GAH	BE	1
159				Student 158	CVS	II	ACC	M	GH	CVS	1
160				Student 159	GAH	TRM	ACC	SDT	RDS	GAH	1
161				Student 160	BE	CVS	Endo	ACC	RM	BE	1
162				Student 161	II	HAEM	RM	BE	Endo	II	1
163				Student 162	II	ACC	M	Endo	HPL	II	1
164				Student 163	Endo	GAH	PH	RM	II	Endo	1
165				Student 164	HPL	II	Endo	RM	TRM	HPL	1
166				Student 165	RDS	NEU	TRM	CF	II	RDS	1
167				Student 166	RDS	NEU	GH	CF	TRM	RDS	1
168				Student 167	TRM	RDS	HPL	SDT	RM	TRM	1
169				Student 168	RDS	HPL	CVS	II	NEU	RDS	1
170				Student 169	M	GH	HPL	HAEM	II	M	1
171				Student 170	NEU	GAH	II	HAEM	CVS	NEU	1
172				Student 171	GAH	Endo	RDS	HAEM	SDT	GAH	1
173				Student 172	CF	GAH	ACC	II	HPL	CF	1
174				Student 173	HPL	RM	HAEM	M	II	HPL	1
175				Student 174	GAH	M	BE	GH	SDT	GAH	1
176				Student 175	TRM	GH	CF	RM	PH	TRM	1
177				Student 176	II	M	ACC	NEU	RDS	II	1
178				Student 177	II	CF	TRM	HPL	GH	II	1
179				Student 178	RDS	TRM	HAEM	II	PH	RDS	1
180				Student 179	II	CVS	HAEM	M	Endo	II	1
181				Student 180	M	HPL	PH	TRM	GAH	M	1
182				Student 181	PH	CVS	GAH	ACC	NEU	PH	1
183				Student 182	RM	M	BE	GH	II	RM	1
184				Student 183	PH	CF	RDS	RM	Endo	PH	1
185				Student 184	NEU	II	BE	GAH	M	NEU	1
186				Student 185	HPL	RDS	NEU	PH	Endo	HPL	1
187				Student 186	II	GAH	SDT	ACC	CF	II	1
188				Student 187	CF	Endo	RM	TRM	NEU	CF	1
189				Student 188	HPL	GAH	CVS	Endo	NEU	HPL	1
190				Student 189	M	GAH	SDT	NEU	ACC	M	1
191				Student 190	RDS	PH	M	II	BE	RDS	1
192				Student 191	M	TRM	HAEM	II	SDT	M	1
193				Student 192	HPL	II	GAH	NEU	TRM	HPL	1
194				Student 193	HPL	NEU	Endo	CVS	ACC	HPL	1
195				Student 194	BE	TRM	ACC	M	Endo	BE	1
196				Student 195	NEU	RM	ACC	GAH	GH	NEU	1
197				Student 196	CVS	HAEM	GAH	BE	GH	CVS	1
198				Student 197	ACC	CVS	GH	TRM	RM	ACC	1
199				Student 198	HAEM	GH	SDT	NEU	RDS	HAEM	1
200				Student 199	TRM	HPL	NEU	M	GAH	TRM	1
201				Student 200	CVS	II	CF	Endo	M	CVS	1
202				Student 201	HAEM	CF	II	GAH	BE	HAEM	1
203				Student 202	II	HPL	GH	Endo	GAH	HPL	2
204				Student 203	HPL	TRM	II	RM	ACC	HPL	1
205				Student 204	HAEM	CF	HPL	ACC	GAH	HAEM	1
206				Student 205	SDT	HPL	GH	GAH	M	SDT	1
207				Student 206	SDT	CVS	CF	GH	Endo	SDT	1
208				Student 207	HAEM	GH	PH	RDS	M	HAEM	1
209				Student 208	PH	BE	M	Endo	RM	PH	1
210				Student 209	RM	CF	SDT	CVS	ACC	RM	1
211				Student 210	RM	GAH	CF	PH	RDS	RM	1
212				Student 211	M	HAEM	TRM	GAH	NEU	M	1
213				Student 212	CF	PH	RDS	NEU	HPL	CF	1
214				Student 213	ACC	NEU	GH	II	CF	ACC	1
215				Student 214	BE	SDT	CF	HPL	TRM	BE	1
216				Student 215	II	GH	Endo	M	NEU	GH	2
217				Student 216	TRM	SDT	BE	RM	M	TRM	1
218				Student 217	PH	RM	HAEM	NEU	SDT	PH	1
219				Student 218	II	ACC	M	GAH	TRM	M	3
220				Student 219	ACC	GH	HPL	M	PH	ACC	1
221				Student 220	CVS	Endo	ACC	RM	SDT	CVS	1
222				Student 221	NEU	HPL	RM	RDS	HAEM	NEU	1
223				Student 222	PH	ACC	M	II	SDT	PH	1
224				Student 223	SDT	HAEM	CVS	HPL	PH	SDT	1
225				Student 224	RM	CVS	M	II	GAH	RM	1
226				Student 225	CVS	ACC	HPL	SDT	Endo	CVS	1
227				Student 226	Endo	PH	II	RM	BE	Endo	1
228				Student 227	TRM	NEU	RDS	CVS	RM	TRM	1
229				Student 228	CF	SDT	M	II	ACC	CF	1
230				Student 229	RM	M	CVS	RDS	NEU	RM	1
231				Student 230	CF	PH	GH	SDT	NEU	CF	1
232				Student 231	RM	SDT	TRM	M	II	RM	1
233				Student 232	CVS	CF	NEU	ACC	SDT	CVS	1
234				Student 233	GH	BE	M	ACC	SDT	GH	1
235				Student 234	CVS	HAEM	M	NEU	RDS	CVS	1
236				Student 235	GH	CVS	M	CF	II	GH	1
237				Student 236	Endo	RDS	CVS	M	NEU	Endo	1
238				Student 237	M	PH	GAH	II	HAEM	M	1
239				Student 238	CF	II	PH	M	GH	CF	1
240				Student 239	CF	SDT	M	RM	Endo	SDT	2
241				Student 240	M	NEU	SDT	TRM	RDS	M	1
242				Student 241	RM	RDS	NEU	II	SDT	RM	1
243				Student 242	GH	ACC	CVS	RM	SDT	GH	1
244				Student 243	HAEM	NEU	Endo	II	M	HAEM	1
245				Student 244	GH	TRM	RM	M	CVS	GH	1
246				Student 245	CVS	CF	SDT	HAEM	ACC	CVS	1
247				Student 246	RDS	NEU	M	SDT	TRM	RDS	1
248				Student 247	II	HAEM	TRM	M	Endo	TRM	3
249				Student 248	CF	SDT	NEU	Endo	GAH	SDT	2
250				Student 249	ACC	HPL	RDS	GH	II	ACC	1
251				Student 250	HPL	Endo	CF	HAEM	SDT	HPL	1
Sheet1

 

[sjf92]

One possible way...
You'll need to set your sheet out exactly as I've indicated below. Note that the Pathway list has been sorted by available spaces (largest to smallest) and that your student list starts in cell D2 - with headers in row 1. I've randomly picked the students' preferences to test the code. Put the code below in a standard module. I've shown the sheet before and after the code is run.
With the 334 randomly selected preferences, it results in 308 getting their 1st preference; 21 getting their second; 4 their 3rd and 1 their 4th. There may be a more equitable way of doing this, but this is what I came up with.

Great thank you, i'll give this a go!

 

[kevin9999]

You don't actually have to sort the pathways by available places - doesn't make any difference.