VBA - Compare cell against matrix with conditions

[ComputerMonkey]

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
[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]Route[/TD]
[TD]Start[/TD]
[TD]Finish[/TD]
[TD]Between1[/TD]
[TD]Between2[/TD]
[/TR]
[TR]
[TD]George Street via Pitt[/TD]
[TD]Depot[/TD]
[TD]Super Mart[/TD]
[TD]Huge Mart[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hume Hwy via Stacey[/TD]
[TD]Collections B[/TD]
[TD]Super Mart[/TD]
[TD]Small Mart[/TD]
[TD]Collections A[/TD]
[/TR]
</tbody>[/TABLE]


Sample Output - Will be using the GPS table (first three fields) and just adding an extra column for the route.
[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Truck Num[/TD]
[TD]Stop[/TD]
[TD]Route (Desired result)[/TD]
[/TR]
[TR]
[TD]28/06/2016[/TD]
[TD]A-1[/TD]
[TD]Depot[/TD]
[TD]George Street via Pitt[/TD]
[/TR]
[TR]
[TD]28/06/2016[/TD]
[TD]A-1[/TD]
[TD]Small Mart[/TD]
[TD]George Street via Pitt[/TD]
[/TR]
[TR]
[TD]28/06/2016[/TD]
[TD]A-1[/TD]
[TD]Big Mart[/TD]
[TD]George Street via Pitt[/TD]
[/TR]
[TR]
[TD]28/06/2016[/TD]
[TD]A-1[/TD]
[TD]Huge Mart[/TD]
[TD]George Street via Pitt[/TD]
[/TR]
[TR]
[TD]28/06/2016[/TD]
[TD]A-1[/TD]
[TD]Collections A[/TD]
[TD]George Street via Pitt[/TD]
[/TR]
[TR]
[TD]28/06/2016[/TD]
[TD]A-1[/TD]
[TD]Super Mart[/TD]
[TD]George Street via Pitt[/TD]
[/TR]
[TR]
[TD]28/06/2016[/TD]
[TD]B-2[/TD]
[TD]Collections B[/TD]
[TD]Hume Hwy via Stacey[/TD]
[/TR]
[TR]
[TD]28/06/2016[/TD]
[TD]B-2[/TD]
[TD]Small Mart[/TD]
[TD]Hume Hwy via Stacey[/TD]
[/TR]
[TR]
[TD]28/06/2016[/TD]
[TD]B-2[/TD]
[TD]Collections A[/TD]
[TD]Hume Hwy via Stacey[/TD]
[/TR]
[TR]
[TD]28/06/2016[/TD]
[TD]B-2[/TD]
[TD]Super Mart[/TD]
[TD]Hume Hwy via Stacey[/TD]
[/TR]
</tbody>[/TABLE]

 

[Ombir]

Hi,

Can you explain how 7 entries are coming for George Street via Pitt and 4 for Hume Hwy via Stacey in GPS table.

Collections A, Small Mart, Big Mart don't exists in Route table for George Street.

 

[ComputerMonkey]

Hi,

Can you explain how 7 entries are coming for George Street via Pitt and 4 for Hume Hwy via Stacey in GPS table.

Collections A, Small Mart, Big Mart don't exists in Route table for George Street.

The Routes table will only look at specific stops, the major locations to determine a route.
i.e. only the "George Street via Pitt" route will have the Origin, Destination and Via pairing of "Depot, Huge Mart, Super Mart"

Sometimes 2 "between" stops are required to differentiate between two different routes. There will be a specific route for each service.