finding all available routings/paths

[akramer08]

I will try to keep this a simple as possible. I have a sheet with a list of flights, the origin of the flight in column A and the destination of the flight in column B. I want to be able to type in an origin and destination in lets say cells F1 and G1, and have a macro determine all possible routings since there is most likely no direct connection.

For example using the table below, if I typed in PDX to BRU, I would want to show three routing combinations;

1. PDX-CVG-BRU
2. PDX-CVG-LEJ-BRU
3. PDX-SEA-CVG-BRU
4. PDX-SEA-CVG-LEJ-BRU

[TABLE="width: 500"]
<tbody>[TR]
[TD]Origin[/TD]
[TD]Destination[/TD]
[/TR]
[TR]
[TD]PDX[/TD]
[TD]SEA[/TD]
[/TR]
[TR]
[TD]SEA[/TD]
[TD]CVG[/TD]
[/TR]
[TR]
[TD]CVG[/TD]
[TD]LEJ[/TD]
[/TR]
[TR]
[TD]CVG[/TD]
[TD]BRU[/TD]
[/TR]
[TR]
[TD]LEJ[/TD]
[TD]BRU[/TD]
[/TR]
[TR]
[TD]PDX[/TD]
[TD]CVG[/TD]
[/TR]
</tbody>[/TABLE]

 

[akramer08]

Anyone have any ideas?

 

[Joe4]

One way would be to use Filters.

If you use simple Filters, you can simply select the "Text Filters" option and enter "begins with PDX" and "end with BRU".
If you want to have an input cell where you enter the Origin/Destination, you should be able to do a similar thing with Advanced Filters.

 

[akramer08]

Simply using filters will not accomplish the goal. I need to search all possible routing combinations with a macro. This way a user can type in an origin and a destination, and the output will show all of the routing combinations in a msgbox, a range of cells, or any other simple view.

 

[Joe4]

Simply using filters will not accomplish the goal. I need to search all possible routing combinations with a macro. This way a user can type in an origin and a destination, and the output will show all of the routing combinations in a msgbox, a range of cells, or any other simple view.

That is exactly what Advanced Filters will do (perhaps you are just not familiar with the capabilities). You can filter in place, or return the results to another location.

To see how this works, here are two macros for you. Open up a new Excel file and run them. The first one simply populates sample data, and a criteria section at the top to show how to filter the example you posted.
The second one runs the Advanced Filter in place to only show the records you want.

Sub Macro1PopulateData()

'   Criteria
    Range("A1") = "CRITERIA"
    Range("B1") = "Route"
    Range("C1") = "Route"
    Range("B2") = "PDX*"
    Range("C2") = "'*BRU"
    Range("A1").Font.Bold = True
    Range("B1:C1").Font.Underline = xlUnderlineStyleSingle
    
'   Data
    Range("A4") = "DATA"
    Range("B4") = "Route"
    Range("B5") = "PDX-CVG-BRU"
    Range("B6") = "PDX"
    Range("B7") = "PDX-CVG-LEJ-BRU"
    Range("B8") = "PDX-SEA-CVG-BRU"
    Range("B9") = "BRU"
    Range("B10") = "PDX-SEA-CVG-LEJ-BRU"
    Range("B11") = "SEA-PDX-BRU"
    Range("B4").Font.Underline = xlUnderlineStyleSingle
    Range("A4").Font.Bold = True
    
End Sub


Sub Macro2ApplyAdvancedFilter()

    Range("B4:B11").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Range("B1:C2"), Unique:=False

End Sub

For more on Advanced Filters, especially as it applies to your situation, check out Example 3 here: https://trumpexcel.com/excel-advanced-filter/

 

[akramer08]

I had a feeling the question would be misunderstood lol. Let me try to explain a little better...

I have a table of two column table of flights:
[TABLE="class: grid, width: 140"]
<tbody>[TR]
[TD]Origin[/TD]
[TD]Destination[/TD]
[/TR]
[TR]
[TD]PDX[/TD]
[TD]SEA[/TD]
[/TR]
[TR]
[TD]SEA[/TD]
[TD]CVG[/TD]
[/TR]
[TR]
[TD]CVG[/TD]
[TD]LEJ[/TD]
[/TR]
[TR]
[TD]CVG[/TD]
[TD]BRU[/TD]
[/TR]
[TR]
[TD]LEJ[/TD]
[TD]BRU[/TD]
[/TR]
[TR]
[TD]PDX[/TD]
[TD]CVG[/TD]
[/TR]
</tbody>[/TABLE]

I need a macro that will take a user input origin and destination, lets say "PDX" and "BRU", and populate all of the possible combinations to get from PDX to BRU based on the available flights listed above.

The macro should determine how many different routes are possible (in this case 4) and could be displayed in a msgbox or in another cell somewhere. I can adjust that part easily.

1. PDX-CVG-BRU
2. PDX-CVG-LEJ-BRU
3. PDX-SEA-CVG-BRU
4. PDX-SEA-CVG-LEJ-BRU

I cannot use filters for "PDX" and "BRU", as that would never show that it is possible to connect on "SEA-CVG" and "CVG-LEJ", in which that is a possible route.

I hope this clears things up.

 

[Joe4]

I cannot use filters for "PDX" and "BRU", as that would never show that it is possible to connect on "SEA-CVG" and "CVG-LEJ", in which that is a possible route.

THAT part is going to be tricky, even for an experienced VBA programmer. I am really not sure the best way to go about that is.
You almost have to go through every record and every combination. I think that could end up being a lot of coding.
Unfortunately, that could be a rather ambitious project, not one that I really have time to commit to right now (and might be a bit much to expect from free help, which may be why no one replied initially).

Perhaps someone already has done something like this, or knows of a clever way to do it. If not, you might want to consider getting a consultant to help you.

 

[MickG]

Try this :-
Results start "D2".

[COLOR="Navy"]Sub[/COLOR] MG09Apr38
'Mg mod
[COLOR="Navy"]Dim[/COLOR] rParents [COLOR="Navy"]As[/COLOR] Range, rNode [COLOR="Navy"]As[/COLOR] Range, rOut [COLOR="Navy"]As[/COLOR] Range, mRng [COLOR="Navy"]As[/COLOR] Range
    [COLOR="Navy"]Dim[/COLOR] lRow [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object
    [COLOR="Navy"]Dim[/COLOR] temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
    [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
    [COLOR="Navy"]Set[/COLOR] rParents = Range("A2", Range("A2").End(xlDown))
    
    [COLOR="Navy"]Set[/COLOR] rOut = Range("D2")
     
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] rNode [COLOR="Navy"]In[/COLOR] rParents
       
        [COLOR="Navy"]If[/COLOR] Not Dic.Exists(rNode.Value) [COLOR="Navy"]Then[/COLOR] Dic.Add rNode.Value, New Collection
        Dic(rNode.Value).Add (rNode.Offset(, 1).Value)
    [COLOR="Navy"]Next[/COLOR] rNode

    
    Call DisplayTree("PDX", Dic, rOut, lRow, 0)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
 
 '[COLOR="Green"][B] Display the tree[/B][/COLOR]
[COLOR="Navy"]Sub[/COLOR] DisplayTree(ByVal sParent [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Dic, rOut [COLOR="Navy"]As[/COLOR] Range, _
    ByRef lRow [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] ByVal lLevel [COLOR="Navy"]As[/COLOR] Long)
    
    [COLOR="Navy"]Dim[/COLOR] ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range, rr [COLOR="Navy"]As[/COLOR] Range
    [COLOR="Navy"]Dim[/COLOR] vChild
   
    [COLOR="Navy"]If[/COLOR] lLevel + lRow = 0 [COLOR="Navy"]Then[/COLOR] rOut = sParent
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] vChild [COLOR="Navy"]In[/COLOR] Dic(sParent)
            lRow = lRow + 1
            rOut(lRow, lLevel + 2) = vChild
        '[COLOR="Green"][B]Added here !![/B][/COLOR]
        [COLOR="Navy"]If[/COLOR] lRow > 1 [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Range(rOut(lRow, 1).Address & ":" & rOut(lRow, lLevel + 1).Address)
                [COLOR="Navy"]If[/COLOR] Not IsEmpty(Dn.Value) [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Exit[/COLOR] For
                    Dn = Dn.Offset(-1)
             [COLOR="Navy"]Next[/COLOR] Dn
        [COLOR="Navy"]End[/COLOR] If
        
        [COLOR="Navy"]If[/COLOR] Dic.Exists(vChild) [COLOR="Navy"]Then[/COLOR]
            lRow = lRow - 1 '[COLOR="Green"][B]< Added Here !![/B][/COLOR]
            Call DisplayTree(vChild, Dic, rOut, lRow, lLevel + 1)
        [COLOR="Navy"]End[/COLOR] If
                                                    
[COLOR="Navy"]Next[/COLOR] vChild
   
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[akramer08]

Mick,

How does the macro know to end the routing at BRU? So if I wanted to change the destination to LEJ, what is the code referencing for an endpoint?

 

[MickG]

It knows the end value because its value is only found in column "B" but not in column "A".
As I only modified this code, I would initially say I'd place the results in an array and remove values I did not want.