Delivery Simulator

[borjasanz]

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:

[TABLE="width: 1000"]
<tbody>[TR]
[TD]Order ID[/TD]
[TD]Time of Order[/TD]
[TD]Restaurant[/TD]
[TD]Client Name[/TD]
[TD]Client Latitude[/TD]
[TD]Client Longitude[/TD]
[TD]Restaurant Latitude[/TD]
[TD]Restaurant Longitude[/TD]
[TD]Time from R to Client[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]13:01:15[/TD]
[TD]1[/TD]
[TD]John[/TD]
[TD]13.0000[/TD]
[TD]14.0000[/TD]
[TD]13.0001[/TD]
[TD]14.0001[/TD]
[TD]8 minutes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.

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

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Restaurant[/TD]
[TD]Time[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]XYZ[/TD]
[TD]13:00:00[/TD]
[TD]Available[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]YZZ[/TD]
[TD]13:00:00[/TD]
[TD]Available[/TD]
[/TR]
</tbody>[/TABLE]

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.