Announcement

Collapse
No announcement yet.

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • $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.
    Attached Files

  • #2
    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!
    _______________________________________________

    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

    _______________________________________________

    Comment


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

      Comment


      • #4
        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!
        _______________________________________________

        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

        _______________________________________________

        Comment


        • #5
          Eastern time zone, but anytime on Monday works.

          Comment


          • #6
            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
            Attached Files
            Check out our new reputation system. Click on the Like button under the post!
            _______________________________________________

            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

            _______________________________________________

            Comment


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

              Comment


              • #8
                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!
                _______________________________________________

                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

                _______________________________________________

                Comment


                • #9
                  Funds received - many thanks!
                  Check out our new reputation system. Click on the Like button under the post!
                  _______________________________________________

                  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

                  _______________________________________________

                  Comment


                  • #10
                    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!
                    Attached Files

                    Comment


                    • #11
                      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

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

                      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

                      _______________________________________________

                      Comment


                      • #12
                        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!
                        Attached Files

                        Comment


                        • #13
                          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!
                          _______________________________________________

                          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

                          _______________________________________________

                          Comment


                          • #14


                            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!

                            Comment

                            Working...
                            X