The way I would do this is to look at the arrival times data and find out what the smallest interval is between two arrivals. This interval interval is the “tick” time.
So the sort the arrivals data into time order.
Then load it into a variant array to make the whole process much faster.
Load the truck load data into another variant
Initialise an output variant array with sufficient rows to account for all ticks
Set up some variable initialised to zero, for number of truck and the total load
You want to start you “simulated clock” just before the first time so create a loop from this start time to just after the last start time with the step being the “tick” time.
Write out the time and current values of the truck load and number of trucks to the output array every iteration
Then you can check this time against initially the first time of arrival in your data and if this time is before or equal to your simulated clock you add that load into total load and add one to the truck count, you then increment a count by one so that the next time you do this check you are checking the second truck to arrive etc,etc.
Just as an example here is some code:
VBA Code:
Sub test()
starttime = TimeValue("06:00:00")
endtime = TimeValue("18:00:00")
indi = 1
For i = starttime To endtime Step TimeValue("01:00:00")
If i > Time() Then ' use the current truck time instead here
Cells(indi, 1) = CStr(i) ' use a variant array instead of writing directly to the cells
indi = indi + 1 ' note you have to keep a separate count of where you are for input and output
End If
Next i
End Sub
