Excel Logic Puzzle: Creating an Employee Pick Sheet

I've been racking my brain trying to figure out how to go about creating a spreadsheet to perform something for me and can't figure out the right angle to approach it from.

So. Here is what I'm trying to do. Numbers are arbitrary to explain.

I have 100 employees listed in seniority order. I have an equal number of jobs for them to pick from.
Each employee picks 5 jobs they wish to do for the next week in order of what they desire.
The highest seniority employee will receive his first choice. The second employee will receive his first choice unless the employee above him picked the same job. If both picked the same job as their first pick then the second employee would receive his second pick.

And so on and so on down the line.

Currently, we do this process by eyeballing it and lots of pencils crossing things off. It usually ends up in numerous errors and having to be second checked.

There must be a way to create a spreadsheet where all you have to do is punch in the employee's picks and then the worksheet will filter it all and spit out which job they will receive.

Personally if I was going to do what you're thinking about, I'd likely use vba scripting within excel, or just forget about excel completely and use vbscript to get the job done (or possibly powershell, but I'm less familiar with the advanced parts of that). Like any other script I'd probably write out a high level summary of the script's logic before starting it.
The first thing I thought of when I read your post, was vbscript's dictionary object, which is an associative array. you could probably do what you want with two dictionary objects, one referencing the staff member's name and seniority and the other, their seniority and then then list of job choices. or you could use tables or normal arrays.

If then else logic would resolve the result once you have all of the data in the script.

...that would be one simple way of doing it and you could do within Excel if you wanted using vba.

As a suggestion, you could have a look through microsoft's online script repository and see if someone's written something close to what you're looking for:

https://gallery.technet.microsoft.com/scriptcenter/

The Excel specific portion of the script repository is here:
https://gallery.technet.microsoft.c...lue=Microsoft Excel&f[1].Text=Microsoft Excel

...I'm not an Excel expert so I can't really give you a quick and easy answer. Someone with better Excel knowledge might have a method for doing this without using scripting - but scripting is the approach I would use.

...Good luck.