Automated Weekly Assignments while looking at several variables

I have a very large project that I do twice a year and it is extremely time consuming and very tedious. I have been exploring options to automate it further then I already have. Here is the general idea of what I am doing. I realize this is very difficult and time consuming so thank you in advance to whomever offers their assistance.

So we have an on-call rotation and our staff is required to participate anywhere from 10-12 weeks a year. We have several different tiers, Tier 1, Tier 2, and Tier 3, Tier HBA, Tier Credit, Tier AUX, Tier SPC. Employees are allowed to submit preferences and rank their preferred weeks twice a year for the weeks only, and we choose the tiers. The first half is weeks 1-22 and the second half is weeks 23-52. Then based on employee rank while taking into account their preferences, they are assigned their weeks of on-call somewhere within the designated time frame splitting up Tiers as evenly as possible. So the number one rank will usually get their desired preferences.

I generally do this based on organization. We have 5 different organization groups.
Org 1 can be assigned – Tier 1, Tier 2, Tier 3
Org 2 can be assigned – Tier HBA
Org 3 can be assigned –Tier Credit
Org 4 can be assigned – Tier AUX
Org 5 can be assigned –Tier SPC

When looking at preferences, I need it to exclude any week that says “Priority PTO Week” as employees don’t get on-call during those weeks. It also needs to treat blanks and zeros as weeks available to schedule. Not all employees submit preferences, which leave the assignments discretionary to me. They will usually be blanks or “0”.
The first half they each receive 3 weeks of on-call and the second half they receive 7 weeks. A few things to take into account would be to make the tiers for Org 1 as even as possible and get a good ratio at each of the three different sites for all Orgs.

Each week however, we only need X amount of staff to be on-call for each tier. We put more weight into certain weeks due to the nature of the business and likelihood additional staff needed. So I need the autonomy to input the number of weeks available for each tier at each site for each of the 5 different organizations.

So I need Excel to be able to do the following with the use of Macros -

1.) House a table of participating employees with preference weeks (keep in mind, one half will be 22 weeks and the other will be 30 weeks)
2.) House a table listing all the weeks we are assigning and the maximum amount of on-call staff needed per Tier (example: 20 Tier 1, 15, Tier 2, and 10 Tier 3)
3.) Have an output sheet with the results. Employee name, site, week, and tier.
4.) Output the results in a calendar type format with each day at the top in row 1, Employee names in Column A, and the tier assignment for each day)

So when the macro runs it needs to look at each employee separate. (They will already be in correct order based on rank). It then needs to look at the employees preferences starting with 1 and going up in order (these may not be in chronological order). If the preference is available and the table showing on-call staff needed isn't at 0, then it would assign that week and move on. It would repeat this process until the employee has been assigned the total weeks needed (Would like to keep this variable where I can update the number of assignments needed easily). It then needs to move on to the next ranked employee and repeat the process until all employees have the desired number of on-call assignments (Generally 3 or 7) depending on which round we are in.

I have attached some sample data as a starting point. The preference sheet is how the preferences will look when imported into the workbook. This is a round two example with 30 weeks. The Calendar Output would be what I need it to look like for all employees / weeks after the assignments are made. This can group all the ORGS together into one master sheet.
Thank you for anyone who takes the time to help me with this. It has been a cumbersome project. Please let me know if you have any questions.