ComputerMonkey
New Member
- Joined
- Jun 28, 2016
- Messages
- 2
Hi,
I am scratching my head with this one and hoping to receive some useful insights/help..
Presently I have two datasets, for the purpose of this example let's call them "GPS Data" and "Routes".
GPS data will list every stop a particular truck will stop at throughout the day (i.e. Super Mall, Small Mall, Tiny Mall, Depot). This data contains information for all trucks operating on a single day.
Routes data is a comprehensive list of each route the truck might take. This is determined by where the truck starts, finishes and any specific stops in between.
What I would like to do is take the GPS data (first three fields of sample output in the table below) and align each stop with the corresponding route. I have already developed code to sort the data by date and truck number, I have also stored variables for:
- Trip First Stop Name
- Trip Last Stop Name
- Length of data (Row count)
- Length of Trip (Row count)
The idea was to run loops to compare each stop with the route table to determine the route and store this into a variable, then fill all cells within the desired range with the output of the variable (Route in sample output). However what I am struggling to achieve is how to code VBA to compare the selected loop cell with the table.
Case statement isn't feasible because I will need to compare against a thousand rows of routes.
Any help is good help!
Route table
<tbody>
</tbody>
Sample Output - Will be using the GPS table (first three fields) and just adding an extra column for the route.
<tbody>
</tbody>
I am scratching my head with this one and hoping to receive some useful insights/help..
Presently I have two datasets, for the purpose of this example let's call them "GPS Data" and "Routes".
GPS data will list every stop a particular truck will stop at throughout the day (i.e. Super Mall, Small Mall, Tiny Mall, Depot). This data contains information for all trucks operating on a single day.
Routes data is a comprehensive list of each route the truck might take. This is determined by where the truck starts, finishes and any specific stops in between.
What I would like to do is take the GPS data (first three fields of sample output in the table below) and align each stop with the corresponding route. I have already developed code to sort the data by date and truck number, I have also stored variables for:
- Trip First Stop Name
- Trip Last Stop Name
- Length of data (Row count)
- Length of Trip (Row count)
The idea was to run loops to compare each stop with the route table to determine the route and store this into a variable, then fill all cells within the desired range with the output of the variable (Route in sample output). However what I am struggling to achieve is how to code VBA to compare the selected loop cell with the table.
Case statement isn't feasible because I will need to compare against a thousand rows of routes.
Any help is good help!
Route table
| Route | Start | Finish | Between1 | Between2 |
| George Street via Pitt | Depot | Super Mart | Huge Mart | |
| Hume Hwy via Stacey | Collections B | Super Mart | Small Mart | Collections A |
<tbody>
</tbody>
Sample Output - Will be using the GPS table (first three fields) and just adding an extra column for the route.
| Date | Truck Num | Stop | Route (Desired result) |
| 28/06/2016 | A-1 | Depot | George Street via Pitt |
| 28/06/2016 | A-1 | Small Mart | George Street via Pitt |
| 28/06/2016 | A-1 | Big Mart | George Street via Pitt |
| 28/06/2016 | A-1 | Huge Mart | George Street via Pitt |
| 28/06/2016 | A-1 | Collections A | George Street via Pitt |
| 28/06/2016 | A-1 | Super Mart | George Street via Pitt |
| 28/06/2016 | B-2 | Collections B | Hume Hwy via Stacey |
| 28/06/2016 | B-2 | Small Mart | Hume Hwy via Stacey |
| 28/06/2016 | B-2 | Collections A | Hume Hwy via Stacey |
| 28/06/2016 | B-2 | Super Mart | Hume Hwy via Stacey |
<tbody>
</tbody>
