Optimisation for Resource Allocation

wjhaw0   This reminded me of another post I helped on:

https://techcommunity.microsoft.com/t5/excel/formula-help-randomly-assign-students-to-groups-based-in/m-p/3691990 

for that post I created a Lambda function and thought it might be of help to you.  Here is the explanation I gave there and the file attached:

 So in the attached I created a complex set of LAMBDA functions include 2 that are recursively calling themselves to solve this challenge.  In the attached workbook is an example:

The inputs to the main LAMBDA call include the RANGE with the selection table (must be 1 column as a unique ID followed by any number of picks) and a Table with the list of options and corresponding max # spots for that option.  

This solution will:

A) randomize the order of the table

B) go down the 1st choices and allow up to the max # for each option

C) continue with each next lower choice columns

D) For EACH row IF that row did not match (i.e. all selected options were already max) then it will auto assign a non-full option based on which ever option has most spots open first. (an example of this is the last line in the image above where E was given even though they didn't select E as an preferred option)

E) Undo the randomized order so that the result correctly lines back up

F) I added in an addition output column to show the 'randomized' order that was used during the process (i found this helpful just for my sake to understand why/how each row ended up getting the choice they got)