filtering until one choice remains

But how:
30 students can choose between 20 ...
But each song may only be used once
So first 20 students will use-out all available songs. How about remaining 10 students.

BTW. Sample workbook would help. So prepare one. May be mini version - say 6 students and 4 songs and mock-up solution manually.

Administrative Note:

Is your forum profile showing the Excel PRODUCT that you need this to work for?

Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

The three most recent Excel products are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

Thanks.

Updated actual Excel version.
If I have 30 students whom can choose 5 songs out of a list of 20 songs in total, then I have 30 students with each 20 songs, then I have 600 possible choises. Of course, some will choose same songs but the preferences of which song they listed first will be determining to give them the song they listed first. In the attached exampel, I mad d-the choice manually...
Thanks so far for the help

Well, The situation presented in a file is only similar to one in description.
There are 29 students
There are unique 26 songs.
Note that in some cells you have either space after song title or a typo. With these, there would be 29 unique titles.

But let's focus on 29/26 situation. 3 songs shall be assigned to 2 students, the remaining 23 one song to one student each.

As students made first-second-third... choice it would be nice to assign then their 1st choice if possible, if not 2nd choice, and so on.
In sample file "The bigger picture", even if present in preferences of Laurens Bintein and Rune Noë was not assigned to anyone.


As for the solution - I don't have (at the moment) wholy automatic one..

I tried solver for whole set, but having few songs which are present only in one or two students choices, and not having all choices of students filled in (one student gave only one answer) didn't led me to acceptable results.

So this semi-automatic is described below.

First I'd locate the songs which are on the list of only one person, these are: All together now, Green Day, Land of the Free, Rage Against, Rock the casbah, The man.
They are conditionally formatted with red font, to be better visible

See the list of unique titles in column P and total count of a song in Q (sorted on Q).

Formula:

Please Login or Register  to view this content.

Iv'e sorted list of students and preferences (columns B:H) based on lowest popularity of their selected song (see column I):

Formula:

Please Login or Register  to view this content.

and obviously students who selected the unique song will be assigned this one, like Julie Ampe and Marie Barbier theit respectively 4th and 1st choice (The nan and Green Day), and so on.

Then for songs selected by two persons: Alright, Help is coming, The bigger picture, What's going on
They are conditionally formatted with green font, to be better visible.

Some choices are pretty obvious also here.
Fee Meersschaert has the first choice of Help is coming (it's also 3rd choice of Michelle Hillebrant, so worse one).
Rune Noë 3rd choice (the same as Fee's) is Alright - obviously assigned.
Laurens Bintein 3rd choice (and 5th of Rune's) is The bigger picture - assigned.
and finally Ella Ghesquière 3rd choice (it's 5th of Margaux) is What's going on - assigned.

all these preassigned songs are marked with green background.

The rest of students - they selected more frequent choices - i tried to use solver for assignment.
I started with assigning all of them their first choice as a solver variable, made constraints that the choice cannot be larger than the total number of their answers - column H:

Formula:

Please Login or Register  to view this content.

and cannot be lower than 1. The next constraint is that choice has to be an integer number, and count of any of assigned songs cannot be larger than 2 (column R):

Formula:

Please Login or Register  to view this content.

By the way - in column L is a title of a selected song:

Formula:

Please Login or Register  to view this content.

And as for goal for solver - I wanted to have as many first choices as possible - if not second choices, and so on. but as some songs are very frequent I decided to weigh this position with the frequency, to promote their selection.

so the weighted choice is (column N):

Formula:

Please Login or Register  to view this content.

and for the goal I used also requirement of possibly small variation. It showed to be mych beter solution than forcing every song being assigned at least once in constraints. But as variation would be a small number, I emphasized it's meaning by multiplying by large factor (1000).

and the goal cell N33 is:

Formula:

Please Login or Register  to view this content.

Well, after running Solver with evolutionary engine we have our results :-)


And final note - the results could and will be different every (almost) time you run solver. This is quite typical for evolutionary engine. But their quality is similar. results of some runs are presented in columns U:Y

It would have been better if the same dropdown list with songs was inserted in column 1 till 4 (1st till 4th choice).
Some students made only a choice for 2 songs where 4 were asked, thta's the reason for the empty cells.
Anyhow, you did a great job for me.. analyzing the problem like you did was anyhow impossible for me.