Allot Seats to Students based on Choice & Percentage

Hello,


I would be very glad if someone could help me on this.


I have attached a sample sheet wherein there are students to whom the subjects have to be allotted based on their percentage. The student with a higher percentage gets first preference in the choice he has marked ( "1" indicating the subject he is most interested in doing ).


One can assume there are 4 seats for this for each subject for this sample sheet.


How to generate an excel sheet containing a list of all the selected students with their allotted choice of subject?




Thanks

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on at least two other forums and you have not provided the required cross-post link(s) here.

Read this to understand why we (and other sites like us) consider this to be important.

https://www.mrexcel.com/board/threads/allot-seats-to-students-based-on-choice-percentage.1179802/
https://www.excelforum.com/excel-ge...nd-percentage-repost-from-another-member.html

Could be done in several ways, so as an indication to me, let's say there's a battle for the last available seat for subject 1:
The 2 contenders might be:
Student 2 with 98% marks having subject 1 as his 2nd preference
and
Student 18 with 43% marks having subject 1 as his 1st preference.

Who gets the last seat?

Would probably use TopN in Dax to solve this however if we keep it as just a straight Excel approach this might help however not nearly as granular as TopN. See if it helps.

https://www.youtube.com/watch?v=-MRyE2V2has

https://www.screencast.com/t/FuJi4Jui