The problem I want to solve is my residential building's garage choices.
There will be a random distribution of parking spaces. I thought that it would be better if each person writes down which spaces they want in order of preference, and then their priority of picking a parking slot is randomized. For instance:
- Person a chooses: p3, p5, p1, p2, p4
- Person b chooses: p3, p1, p2, p4, p5
- Person c chooses: p1, p3, p2, p5, p4
- Person d chooses: p1, p5, p2, p3, p4
- Person e chooses: p2, p3, p4, p5, p1
Then we would simply randomize the persons a thru e and it would be easy to define who would get what.
I want to use Google Forms to gather people's choices of priorities. Then I would randomize the order of choosing using random.org. And then I want to use Google Sheets to give the best spot to each person.
This mock-up data set has only 5 persons and 5 parking spots. The real life data set has 100 persons and 100 parking spots.
Gathering data is simple, randomizing is also easy. I’m having troubles coming up with the formula that would check what has already be chosen, return the best available spot for that person, and then jump to the next.
It seemed simple, but I tried a bit with vlookup (and it would lead to a enormous clumsy formula) and I have had poor success with index / match.