Crew Compliment Automation in Excel

Hi Excel Community,

I've been racking my brain on a project on excel, it is very simple but yet very complicated as i have never done it before.

https://preview.redd.it/p9xclu4gxoog1.png?width=1833&format=png&auto=webp&s=6a9bf2bf379b3e62b0d34c692e250cf198190665

Problem statement: As you can see in the image, on the left i have a list of resources available such as Crew Leaders CL, A Mechanics, B mechanics and H Helpers available. and then in the table down below i have a list of tasks/WO's with start and finish dates with FTE counts and Man Hrs required to complete the tasks.

now on under May 2026 i have the list of available resources that i automatically fill up based on the resources i enter in the B and C column and under that i have a conditional formatting to show if I'm exhausting these resources based on how i assign them.

Each resource can work more than 40 hours a week if required.
I need to make crews and assign them to these jobs every week and perform permutation and combinations so that i don't have a lot of delinquent resources on a job, i could but i don't want to have.

now there are some rules that i follow while i create the crews, for example
If FTE <= 5 then I assign 1 CL and then go for 3 A’s and then a B or H if available to fill the resources and if B and H are not available then Backfill with A.

IF FTE > 5 then I assign 2 CLs and then go for maximum A’s and then then a B or H if available to fill the resources and if B and H are not available then Backfill with A.

If the station and the early start date are the same, then assign the same crew to the WO (since they can work on more than 2 tasks the same day/week)

- I Look at the start date and the station (alphanumeric) and FTE count and assign the same crews and mark them as * if repeating

I want to automate this crew assignments, since this is taking most of my time, maybe use VBA and click a button and it spits out a dummy sheet with WO's with all the crews created automatically and tells me how many i need to refill from a different dept or office.

I would love to collaborate and learn, Thanks in advance