Using some priority rankings and the =RAND() function, I have successfully created a lottery to give preference for different troops to be get their first choice at booths to raise money for a non-profit. Each troop has a booth preferences, for example 50 would be location B time slot 10, which they ranked.
I am struggling to assign/match the 1st troop their 1st preference and then remove that option from the pool so that the 2nd in line in the lottery can't be assigned that location/time but would get the top preference if not already taken. In the spreadsheet attached I have 10 troops which are ranked (column G) randomly to be assigned first round of preferred booth assignments, which I am trying to put into column H based on each troops top 25 preferences in columns I:AG. Note that the troops many rank < locations/times.
Thanks in advance.
