assigning 20 vans to 50 employees based on preference

[Katrix198206]

Hi,

I have to assign vans to my employees every morning. Each employee has a preference of which Van they want to use. Currently I am doing this by hand and comparing a sheet with employees and preferred van and trying to assign them to their van. I have 50 employees but 20 vans, so all 50 employees have a van preference from 1 to 20, repeated as needed. If it was 20 vans and 20 employees I would just use Vlookup to assign the vans. With 50 employees and 2 employees with same preference working the same day I am not sure how I can accomplish this.

I have a 3 sheets, one sheet (Vans) has van numbers 1 to 20. Second sheet has (Employees 50-75 with a preferred van number). I want to know if there is a way I can make it so that if an employee name is added to a (Blank) third sheet it would do a Vlookup to see which van the employee prefers, but if the Van is already assigned to another employee, it would pick another unassigned van for the employee.

I can get van preferences from employees where they pick 1st 2nd 3rd 4th 5th preferred van if needed.

Is there a way to do this in Excel?

Thanks.

[ChemistB]

My first question is what happens to 30 people without vans? Do they go home? Or do you only have a maximum of 20 employees working on a given day?

It sounds like you'll probably need VBA to create a list of who had the van last so they go to the bottom of the list.

[alansidman]

crosspost: https://www.mrexcel.com/board/thread...ookup.1141046/

Rule 03: Cross-posting Without Telling Us

Your post does not comply with Rule 3 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

Post a link to any other forums where you have asked the same question.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Read this to understand why we ask you to do this.

I have added the crosspost reference for you today. Please comply with this and all our rules in the future