finding all available routings/paths

akramer08

Active Member
Joined
May 2, 2012
Messages
265
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

OriginDestination
PDXSEA
SEACVG
CVGLEJ
CVGBRU
LEJBRU
PDXCVG

<tbody>
</tbody>
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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.
 
Upvote 0
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.
 
Upvote 0
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.
Code:
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/
 
Upvote 0
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:
OriginDestination
PDXSEA
SEACVG
CVGLEJ
CVGBRU
LEJBRU
PDXCVG

<tbody>
</tbody>

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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
Try this :-
Results start "D2".
Code:
[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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top