$50 - Resource Allocation based on start time and scheduled work

Olaura · Jan 18th 2019

In the attached file, I have the available resources based on their start time in columns B:C.
I have the desired work that needs to be assigned to each resource and their start/end time. What I want to do is assign a driver based on the start and end time of each run.
So for runs 1-3 just assign based on if there are resources available based on start time(dynamic data validation sheet) . For the rest, I want to check first if the drivers 1-3 are finished with their first assignment so they can take up the next one, if they're not then assign the next resource from the list in columns B:C.

I've used decimal times to make it easier to calculate like if start time is less then end time, then assign the next resource, otherwise assign the resource that's done from the ones above.

Not sure if I've explained this properly, please let me know if more clarification is needed.

**Ger Plante** · Jan 18th 2019

Hi Olaura,

4 Quick questions...

1. in your example, on row number 11, you gave that job back to driver number 1. Why not give it to driver number 9, who had a start time at 5.25, and was unallocated at that time. Similarly I suppose for drivers, 10, 11 & 12 - why didnt they get any job when they seemed to be available.
2. Is a vba solution OK (Macros)?
3. When do you need it by?
4. Did you pay 10% to ozgrid as per the rules? (I personally dont have visibility of payments).

Thanks,
Gerard

Olaura · Jan 18th 2019

Thank you for your response Gerard.

Answers below:

1. So I want to optimize the drivers who are already on the "clock", so if I don't need 10,11,12, I can delay their start time maybe
2. I would like both... a formula solve and a Macro solve for future development if possible
3. I have to review this early next week, so ideally Monday afternoon
4. I did pay the 10% to ozgrid... the timing wasn't exactly how they requested, I think I paid minutes after I posted. How can we validate if it's ok?

Thanks!

**Ger Plante** · Jan 18th 2019

Hey...

Monday afternoon is relative to your physical location. what time zone are you in?

I don’t think this can be done with a formula to be honest. Might have to be a macro. Does that become an issue for you?

No need to validate the payment, I trust you.

Olaura · Jan 18th 2019

Eastern time zone, but anytime on Monday works.

**Ger Plante** · Jan 20th 2019

Alright. No problem. By Eastern Time Zone, I wasnt sure if you meant Japan, eastern Europe or East coast of US but it doesn't matter, I think I have it completed.

Please see attached and run the macro "Do_Allocations" and let me know if this is what you wanted/needed.

The VBA code may need to be tweaked a little if your "actual" data is in different rows or columns, but I commented the code as best as I could so you can either try to update it yourself or let me know and I can update it for you, but certainly for your sample data it seems to match your expected results.

I dont think this can be done with formulas - or certainly not very straight forward anyway.

Regards,
Ger

Olaura · Jan 20th 2019

Thank you! Going to try and adjust for my actual data and see how it goes. How do I complete the rest of the payment? Does it go directly to you? If so, send me your paypal account and I will send it over.

Thanks again!

**Ger Plante** · Jan 21st 2019

Hi Olaura - let me know if you need further assistance.

I sent you a private message with my paypal details.

Thanks,
Ger

**Ger Plante** · Jan 22nd 2019

Funds received - many thanks! :cheers:

Olaura · Jan 23rd 2019

Hey! Follow up question... I tried with my actual data, but due to the 5 min increments I used for time, it didn't assign 2 jobs because it started 3 minutes before the driver available finished their previous job,
Is there a way to make it wait for the closest available?
I've also provided a gantt view of how I normally map these tasks, would it be easier to go off the gantt to assign jobs once the previous one is finalized?

Thanks!

**Ger Plante** · Jan 23rd 2019

Firstly, after you added the Gantt chart it effected the headers a little bit, so your driver job allocations were in the wrong place. I've updated that now (attached).

Secondly, I'm not sure I "See" the problem, I can see that the Gantt is in 5 minute intervals and this looks correct for the drivers. Can you show me a specific example of where "it didn't assign 2 jobs because it started 3 minutes before the driver available finished their previous job".

Thx.
Ger

Olaura · Jan 23rd 2019

In the sample data this doesn't happen, but in my real data it does... I've included the actual data in sheet 1 so you can see.

THanks!

**Ger Plante** · Jan 23rd 2019

Thanks, just want to be sure I understand, you are referring to gaps, where no driver was assigned? This happened because no driver at all was available at this time... all of them were utilised.

so we would need to change the logic a bit to
1. Assign the job on first available basis.
2. If all drivers are in use for a job, then Assign a job to the next available driver (closest in time)....

Is that right? So at the end, no job should be unassigned.... ?

This might be trickier than it seems

Olaura · Jan 23rd 2019

You are correct. That's why I thought it might be easier using the gantt chart with the "1" value and maybe using a lookup formula, but I haven't been able to figure that out either.

You can send me a charge request for this other solution if you need to.

THanks!

$50 - Resource Allocation based on start time and scheduled work

Participate now!

Share