Copy, order and group data by certain rules (Create a transportation service sheet)

[operador84]

Hi!!!
Everyday I have to order and group a list of clients into several transport services, regarding their arrivals. So from the airport to their booked hotels.

Here is a link to an example file with that list at the sheet named: "chegadas".
https://www.dropbox.com/s/qla0orcpfa2myh1/Chegadas%2020.02-alt.xls?dl=1

The process is the following:

1- Order from A to Z by column VOO ( which is ARRIVAL FLIGHT data)
2- Group by hotel area (at "hoteis info" sheet)
3- Group until 8 pax (max veicule capacity)
4- Create shortest route between airport (starting point) and hotels of each service.

Sheet "chegadas-2" has a example of steps 1 to 3.
For step 4 I thought of creating a distance matrix (like at sheet "distâncias" - missing some hotels).
And use a solution like this: https://www.youtube.com/watch?v=-E3rSoClgMI

Is this possible to do this in Excel automatically with VBA and/or formulas??
Thank you in advance!!!

 

[operador84]

NOTE: There are 3 exceptions that are not grouped in 8 with other rows, for step 3:
Column: S/TRSF (client does not have transfer), when cell VOO is empty(no flight) and C/PRIVILEGE ( client has a private transfer)

 

[operador84]

Hello? Can anyone help please?