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

  • 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.

  • 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

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • 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!

  • 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.

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • Alright. No problem. By Eastern Time Zone, I wasnt sure if you meant Japan, eastern Europe or East coast of US :thumbup::thumbup: 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

    Files

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • 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!

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


    I sent you a private message with my paypal details.


    Thanks,
    Ger

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • Funds received - many thanks! :cheers:
    :-D

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • 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!

  • 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

    Files

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • 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 :)

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • 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!