Crew Compliment Automation in Excel
Our take
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.
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
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Creating an Auto Scheduler for my Work’s DepartmentI realize I may go down a rabbit hole and this could potentially take me months or maybe even longer, but that’s fine I just need starting points for my research. My experience: Not a lot. Limited to extremely basic functions (e.g. SUM, IF, AND, OR), conditional formatting, data validation. Willing to delve into VBN but I know that requires basic knowledge of programming. Goal: There’s ~40 people in my department and we have an excel sheet that tracks all the daily tasks that need to be completed. There’s myself and a manager that makes the schedule for our respective sides of the week and it takes us 2-3 hours each week to assign each task one by one until the schedule is filled out. I would like to automate this in some kind of way. Ideas: - Assign a theoretical/average time to complete a task. - Set each task as either an AM or PM shift specific task, and other tasks that can be completed by either shift. - Assign people to a specific shift, and how many hours their shift is and on what days. - If people have planned PTO, being able to update the available analysts that Excel will take into account. - Excel does the work of auto-populating tasks with a person for each task accounting everything above and not assigning tasks that would cumulatively exceed the number of hours they’re at work, as well as averaging the workload between each person. - Dynamic and editable after cells are auto-populated. Truthfully this is not required, but a quality of life. People call out, emergency situations might come up that might require attention over daily tasks, etc. My thought is I can copy the results and paste it into a separate sheet that can be editable as the week goes on. Again I’m sure I have no idea how much work I’m asking for and it may be way above my capabilities, but as you roast me in the comments at least leave something that I can use to help me get started. Appreciate yall. submitted by /u/GoldJomell [link] [comments]
- Creating an auto-populating visual calendar for multiple departments to view?I coordinate student schedules across multiple hospital units. I log the shifts in an hours log spreadsheet (date, start time, end time, unit, student name, etc.). Every month, I need to send each unit a schedule showing who is coming in and when so they can review and plan coverage. Right now, this part is very manual, and does not lend to tracking any data like how many students on each units, hours, etc., which we need as a department. We have over 100 students each month, so as much automizing as possible would be helpful as its a big process. Ideally, I’d like something that: Pulls directly from the hours log (no retyping or copy/paste) Is easy to read at a glance for unit managers Can handle many units, many months, and many students Lets me filter by unit + month I'm not really an excel genius, and I've been using copilot to help me with some of this and formulations, but It's not providing me with accurate formulas for the actual calendar conversion. If there are any resources on what formulas to use, what platforms that I could maybe look into for how to do this, or just honestly any advice at all. We've gotten the data tracking mostly drafted up and it's able to pull from pivot tables and show the analytics, but the calendar is my biggest hurdle at the moment. submitted by /u/JuggernautFlashy6489 [link] [comments]
- Issues with scaling a work roster scheduleUsing a leave register I am trying to automate an AM/PM work roster. The Leave Register counts blank cells to confirm who's available, & I'm filtering a randomised list of available staff in a separate column (N:Q in image 2): =LET( P1AM, FILTER('Leave Register 2026'!A92:A98, 'Leave Register 2026'!C92:C98 = ""), SORTBY(P1AM, RANDARRAY(ROWS(P1AM))) ) https://preview.redd.it/y16be41qopqg1.png?width=1008&format=png&auto=webp&s=6ecbcd87549c4537f331b71a0b38150d0ff1d409 I'm using variations of the formula below in C11:C28 & D11:D28: =LET( P1AM, N4#, UsedNames, VSTACK(range to check to avoid duplication), AvailableNames, FILTER(P1AM, ISNA(XMATCH(P1AM, UsedNames)), ""), IF(ROWS(AvailableNames)=0, "", INDEX(AvailableNames, 1)) ) The Sales cells (Rows 4 & 7) are manually entered by the user at the start of the week, so each cell below this checks these before populating a name, along with any cells populated prior to the specific cell in question. As each cell has a different range it's checking, this seems hard to grow without manual entry across a week let alone at an annual scale. https://preview.redd.it/h5dqw6uqopqg1.png?width=1248&format=png&auto=webp&s=38ca2bdc5e3e809779a4391437fdc8227b3ee538 submitted by /u/FishingElbow [link] [comments]
- Weekly changing roster to track changes while maintaining data regarding completed assessments.Alright, I will try to explain this as clearly as i can. There is a constant movement of clients in and out of my job. I need to track whether or not an assessment has been done for each client, but the population is constantly fluctuating. There is a weekly roster that comes out to compare last week's clients to this week's. What I would like to do is take information from spreadsheet W (the weekly roster sent out), and update spreadsheet T (the one to track my assessments). The attached picture is spreadsheet T. The yellow highlight on the "Old List" indicates that the assessment is done, the "New List" was directly pasted from spreadsheet W. Column K is as far as I got with conditional formatting as the formulas is: =IF(ISERROR(MATCH(I3:I113, C3:C113, 0))=TRUE, "New", "Old") I want to the highlight to automatically transfer over to the "New List" and then going forward that would be copy/pasted to the "Old List" the following week. https://preview.redd.it/jvpvjakm37vg1.png?width=625&format=png&auto=webp&s=590c0504766533da8e55605c9fc545136c26baa0 submitted by /u/No_Spend1267 [link] [comments]