Giving each person in order their top choice which is still available in Google Sheets

Here's a sheet I made that does it for you.

Parking Space Allotter

The strategy:

1. Column A is a person's name.
2. Columns B-F are that person's top choices in order.
3. Column H picks a random sort order for that person. (There is no collision detection, but with only 100 people, the chances of collision are very slim. You can also regenerate the sort order if needed.)
4. Columns J-O are a copy of Columns A-F, but sorted according to the sort order.
5. Column Q is the space allotted to that person. Essentially, the first person gets their top pick. The next person gets the highest pick that has not already been picked.

As I noted in a comment, with 100 people, 100 spaces, and only 5 picks, it's possible for people not to get any of their picks. For example, if six people A,B,C,D,E,F choose the same top 5 picks 1,2,3,4,5, A will get 1; B=2; C=3; D=4; E=5; and person F will not be able to get one of their picks. If some spots in your lot are clearly more desirable than others, this is not an unlikely scenario.

The "algorithmic" solution is to make everyone order their picks for all 100 spaces, but that's unfeasible. :)

6. Column R assigns a random unallotted space to the person if they could not get one of their picks. (I have not implemented this at time of writing... Happy to have people's suggestions.)

Two more minor notes:

• In the wild, you would want to protect some ranges to ensure people can only change their own picks and interact with nothing else. You might even want to create a separate spreadsheet where you collect the picks, and then copy them in here yourself.

• This is an XY challenge, but while Google Sheets can allot spaces like this, it can't really optimize the distribution. For that you would want to go for a programming language so you can do more complex scenario-testing. Some scenarios are better than others as judged by how many people get their preferred picks, so a purely random order may not be optimal. I actually just did some work in Python allotting club membership to students from my school in such a way as to optimize the number of students getting into their highest possible picks; the algorithm is quite involved, but if you're interested, you can read about it here.

I do not know how to do this on Google Spreadsheets either, but there is an algorithm (Gale Shapley Algorithm for Stable Matching problem) that has runtime of $O(n^2)$ which is much better than $O(n!)$ for the other answer, and is also optimal.

For details, see https://en.wikipedia.org/wiki/Stable_marriage_problem#Algorithmic_solution and https://en.wikipedia.org/wiki/Gale%E2%80%93Shapley_algorithm.

The Gale–Shapley algorithm involves a number of "rounds" (or "iterations"):

In the first round, first a) each unengaged man proposes to the woman he prefers most, and then b) each woman replies "maybe" to her suitor she most prefers and "no" to all other suitors. She is then provisionally "engaged" to the suitor she most prefers so far, and that suitor is likewise provisionally engaged to her.

In each subsequent round, first a) each unengaged man proposes to the most-preferred woman to whom he has not yet proposed (regardless of whether the woman is already engaged), and then b) each woman replies "maybe" if she is currently not engaged or if she prefers this man over her current provisional partner (in this case, she rejects her current provisional partner who becomes unengaged). The provisional nature of engagements preserves the right of an already-engaged woman to "trade up" (and, in the process, to "jilt" her until-then partner).

This process is repeated until everyone is engaged.

In your problem, the "unengaged man" are the people without a parking lot assigned, and "unengaged woman" are the parking lots.

The problem can be seen like this: the goal is to assign a spot $s_i$ to every person $p_j$, with $1 \leq i \leq N$ (number of spots) and $1 \leq j \leq M$ (number of persons).

I'm rusty about combinatorials, but if I'm not mistaken this means that an assignment is an ordered sequence of $M$ elements chosen among $N$ without repetition. Thus this a permutation without repetition and the total number of possible assignments is:

$$\frac{N!}{(N-M)!}$$

If $M=N$ this is the same as regular permutation: $N!$

The brute-force solution would be write a code which enumerates all the possible assignments and assigns a score to each of them based on the preferences. For example this score could assign 10 for every person who has their first choice satisfied, 9 if they have their 2nd choice satisfied, ... until 0 if none of their 10 top choices is satisfied. After calculating this score for every possible assignment, the solution which obtains the maximum score is optimal: it satisfies the highest number of people with their most prefered option. Of course the scoring is very important, for example a more heavy penalty might be counted when somebody has none of their preference satisfied.

This requires a bit of programming, I don't think this is doable with Google Sheets only (at least I don't know how).

Even with programming, this is going to be tricky with $N=100$, because $100!\approx 9.10^{157}$...

For this kind of problem I would normally recommend a genetic algorithm, which should be able to find a local optimum much faster than the brute-force version. It's a bit advanced of course.