I try to assign students to courses based on the student's preference for each course and course capacity. Students are ranked based previous performance. In the current implementation, the data is obtained via Google Forms and other data sources. The aim is to use arrayformula() everywhere so that no manual processing would be required.
A very simplified sample data sheet without any formulas or external connections can be found here: sample data
Top students are at the top and their choice is processed first. A possible desired output (calculated manually) is also shown.
I can, with array formulas easily identify the first choices and calculate the accumulated sum for each course, but where I'm struggling with is applying the constraint once a class has reached it's limit: my formulas start having circular reference errors or out of bounds errors (when using offset's).
I would appreciate some guidance from the community.