Transportation Problem

[slatten]

Looking for some advice here. I am trying to build a calculator that provides a solution of the total number of hours it would take to transport a certain number of people to different areas using a known number of vehicles. I would like to be able to build this so that the number of destinations is dynamic, meaning I can add more if need be. The total transportation time will be known and so will the number of vehicles being used. For instance, say I am taking 5 people to destination A which is a total turnaround time of 9 hours, 3 people to destination B which is a total turnaround time of 5 hours, and 5 people to destination C which is a total turnaround time of 4 hours. I will be using 5 vehicles. I need to know how long that will take. I have built something but it does not take into account the fact that all the vehicles would be leaving and traveling simultaneously. Any help would be greatly appreciated.

[shg]

It will take 9 hours, and two vehicles will be idle, no?

[slatten]

No, all vehicles should be running at the same time to transport the people as fast as possible.

[shg]

Only one person per vehicle?

[slatten]

Correct. One person per vehicle.

[shg]

So does this look like a solution?

	A	B	C	D	E	F
1	Dest	Time
2	A	9:00
3	B	5:00
4	C	4:00
5
6	Name	Dest	Time	Vehicle	Depart	Return
7	Alan	A	9:00	1	0:00	9:00
8	Barb	A	9:00	2	0:00	9:00
9	Cain	A	9:00	3	0:00	9:00
10	Dana	A	9:00	4	0:00	9:00
11	Eric	A	9:00	5	0:00	9:00
12	Fran	B	5:00	1	9:00	14:00
13	Gary	B	5:00	2	9:00	14:00
14	Hana	B	5:00	3	9:00	14:00
15	Ivan	C	4:00	4	9:00	13:00
16	Jane	C	4:00	5	9:00	13:00
17	Kent	C	4:00	4	13:00	17:00
18	Leah	C	4:00	5	13:00	17:00
19	Mark	C	4:00	1	14:00	18:00

[slatten]

No. Not necessarily looking for a detailed trip time solution, but rather, just the total number of hours it will take to complete all the trips with the vehicles running simultaneously.

[slatten]

Here is an example of what I have done however, this solution does not take into account vehicles operating simultaneously. And in this, I have made it so that "Total Ambulances for Evac" and "Pt. Number" are the main variables. Of course so are the different time columns.
Capture.PNG

[shg]

Short of VBA, I don't see how you can calculate the total time without showing the actual dispatch plan (VBA would still have to calculate it, but wouldn't have to show it).

Also, for minimum elapsed time until the last vehicle returns (make the drivers happy), you would dispatch the trips longest to shortest. For evacuating the largest number of people in the shortest amount of time (fewest crispy critters), you would dispatch the trips shortest to longest.