Delivery Simulator

borjasanz

New Member
Joined
Sep 17, 2013
Messages
5
Hi.

I work at a fast food company in Latin America and I'm trying to simulate a food delivery model. I have managed to simulate the customers that happen during a certain hour (for example, Friday at 1pm). This is done by using a normal distribution with the average and standard deviation for each restaurant.

This is working fine.

Here is what I have now:
A list of orders placed to different restaurants along with the latitude and longitude of the customer and that of the restaurant. This allows me to place a call to the API of Google Maps to have an estimate of the time taken from the restaurant to the customer.
Until now everything is working well, I have all required information needed.

This is how the table looks like:

Order IDTime of OrderRestaurantClient NameClient LatitudeClient LongitudeRestaurant LatitudeRestaurant LongitudeTime from R to Client
113:01:151John13.000014.000013.000114.00018 minutes
2

<tbody>
</tbody>

With this data I have created another table that adds preparation time, and also coming back from the restaurant in minutes (also calculated with Google maps)

Now, I have managed to create a very short Macro that simulates an hour of time with a pause of 1 second during each minute.

Code:
Dim i As Integer



For i = 1 To 60
    Cells(3, 3).Value = Cells(3, 3).Value + TimeSerial(0, 1, 0)
    Cells(3, 4).Value = Cells(3, 4).Value + TimeSerial(0, 1, 0)

Next i
This increases one cell by each minute and also the cell right next to it (to give me an interval of one minute)


Now is where the hard part begins.
I have to dinamically start assigning orders to deliverers (I have a list of all the delivery persons by restaurant) with a Status that says either Available or Not Available and also a time.

So it looks something like this

IDRestaurantTimeStatus
1XYZ13:00:00Available
2YZZ13:00:00Available

<tbody>
</tbody>

I would need the macro to run through each of the orders (remember I have the time that every order came in and also which restaurant the order is going to be delivered from) then assign it to one of the agents.
The way I thought of this was to validate if the order came in later than the time of the deliverer and if the agent is in the same restaurant as the order that came in, then it gets assigned to him. Then the time would be substituted by the time that this agent is available again (I already have it calculated in the other sheet)

Please help, I understand there has to be some kind of loop that checks each row of the table, then checks the condition (that the hour is higher than the time in table) and then replaces that time.
Then it goes for the next order and so on.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Forum statistics

Threads
1,143,637
Messages
5,719,972
Members
422,253
Latest member
frankie2016tata

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top