multi level relationship challenge

[Jaimarti]

Hi, I am new to the Forum and look forward to your feedback. I am using EXCEL 2007 but am open to other options. I am familiar with using VBA. I am having an issue getting my head around what to do to accomplish the following. I have two Columns of data that represent a parent child relationship and each child may have siblings as well as be a parent. All information is built based on the two columns of data. Here is simple example of data. I am not sure how many generations of data there is I have seen up to twenty. But it is hard to know until I can get this done. The output of the exercise is to be able to output a list of dependencies. </SPAN>

Data Example</SPAN>
Parent Child </SPAN>
AZPHNX-DEVILS MOUNTAIN, AZPHNX-I-17 & KACHINA VILLAGE </SPAN>
AZPHNX-DEVILS MOUNTAIN, AZPHNX-VOLUNTEER MOUNTAIN </SPAN>
AZPHNX-VOLUNTEER MOUNTAIN, AZPHNX-I-17 & EXIT 236 </SPAN>
AZPHNX-MOUNT ELDEN, AZPHNX-DEVILS MOUNTAIN </SPAN>
AZPHNX-MOUNT ELDEN, AZPHNX-TWIN ARROWS</SPAN>

Tree view of example </SPAN>
Currently there are 5000 Rows of data. I do not need to build an association for every Parent Child etc. relationship at once. I would like to right click on a parent name select build dependencies list( I know how to do the right click and add to menu portion)and execute the code to run through the 2 columns of data and build basically the family tree down to the furthest relationship. I do not have a current need to look the other way to build an ancestry for the parent selected. I have tried several different methods but end up crashing Excel, only first level relationships or missing relationships. I know that there must be a simple solution but it eludes me for the present. </SPAN>

 

[Jaimarti]

Wow it was hard but I figured it out tonight still have a few issues but that was the bigest hurdle. There are actualy three rows of data. So I guess I will mark this solved.

Range("C:C").AutoFilter Field:=3, Criteria1:=Application.Range("C" & rCell & ":L" & rCell).Value, Operator:=xlFilterValues


Set rng = Range("A2", Range("A65536").End(xlUp)).SpecialCells(xlCellTypeVisible)
Set rData = Sheets("Dependancy List").UsedRange 'Identify the Tab to copy from
Set rVis = rData.SpecialCells(xlCellTypeVisible)
'Loop therough iterations of cells to filter on visible cells
C = 0
Do Until C = 20
Range("C:C").AutoFilter Field:=3, Criteria1:=Application.rVis, Operator:=xlFilterValues
DoEvents
C = C + 1
Loop

 

[Jaimarti]

Sorry I stand corrected I still need to figure it out I am not getting all iterations, So I am open to sugestions.

 

[Jerry Sullivan]

Hi Jaimarti,

Could you post an example of what your desired "dependency list" result would look like?

From the description in your OP, I think a list could be built with VBA.
A recursive function could find a list of children for a specified parent, then call itself to find the children of those children and so on until all nodes with no children are reached.

What isn't clear to me is how you would want the resulting matches to be displayed or stored.

 

[Jaimarti]

Thank you for the reply, First it would display each branch of the in the example Mount Elden has two branches Devils Mountain and Twin arrows and continue to build upon it. once all branches are built then I would index all locations for a list. I have the code to do the index working I just have not been able to build a complete list I am not set on the approuch to getting the data I just need to list all associations in a list.</SPAN>
[TABLE="width: 383"]
<TBODY>[TR]
[TD]</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

[TABLE="width: 1321"]
<TBODY>[TR]
[TD]A Location Name</SPAN>
[/TD]
[TD]Dep 1</SPAN>
[/TD]
[TD]Dep 2</SPAN>
[/TD]
[/TR]
[TR]
[TD]AZPHNX-DEVILS MOUNTAIN</SPAN>
[/TD]
[TD]AZPHNX-I-17 & KACHINA VILLAGE</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AZPHNX-DEVILS MOUNTAIN</SPAN>
[/TD]
[TD]AZPHNX-VOLUNTEER MOUNTAIN</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AZPHNX-MOUNT ELDEN</SPAN>
[/TD]
[TD]AZPHNX-DEVILS MOUNTAIN</SPAN>
[/TD]
[TD]AZPHNX-I-17 & KACHINA VILLAGE</SPAN>
[/TD]
[/TR]
[TR]
[TD]AZPHNX-MOUNT ELDEN</SPAN>
[/TD]
[TD]AZPHNX-TWIN ARROWS</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
Dependency list would be a list of locations. hope this helps.

[TABLE="width: 195"]
<TBODY>[TR]
[TD]AZPHNX-DEVILS MOUNTAIN</SPAN>
[/TD]
[/TR]
[TR]
[TD]AZPHNX-I-17 & KACHINA VILLAGE</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]</SPAN>
[/TD]
[/TR]
[TR]
[TD]AZPHNX-VOLUNTEER MOUNTAIN</SPAN>
[/TD]
[/TR]
[TR]
[TD]AZPHNX-MOUNT ELDEN</SPAN>
[/TD]
[/TR]
[TR]
[TD]AZPHNX-TWIN ARROWS</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

 

[Jerry Sullivan]

Are both of those tables the result you want from right-clicking on AZPHNX-MOUNT ELDEN?
I can see that set of relationships, as shown in the screen shot below, but I'm not following why the 4 rows of relationships are listed under "A Location Name".

Excel Workbook
	G	H	I	J	K	L	M
1					AZPHNX-I-17 & KACHINA VILLAGE
2			AZPHNX-DEVILS MOUNTAIN
3	AZPHNX-MOUNT ELDEN
4					AZPHNX-VOLUNTEER MOUNTAIN
5			AZPHNX-TWIN ARROWS
6							AZPHNX-I-17 & EXIT 236
7
Sheet

Could you explain the basis for that and whether the sequence of listings matters?
If the Dependency list is based on right-clicking AZPHNX-MOUNT ELDEN, should AZPHNX-MOUNT ELDEN be included in the list as you show it?

 

[Jaimarti]

JS411 thanks for the reply. The database only shows 2 columns “A location name” and “Dependency 1” as shown in my original post. Based on that information the rest needs to be correlated, in the example the additional dependencies were done with VLookups that data to include only first tier children if that makes sense. What I am trying to accomplish in the code I attached in my latter post is to take all the sites identified and refilter the list based off of the revised list of sites. I then want to repeat until I find no new sites this should allow me to identify all sites in a chain. I am open to a different way of accomplishing this not just the way I have explained. I am attempting to find all correlation between two rows with the starting point of one location and dependency combination. So that would begin with anything that Matches A location as a parent and anything that matches Dependency 1 as a parent, and repeat for the next iteration of Parents and dependencies that are identified until none are left. I hope this makes sense. </SPAN>

 

[Jerry Sullivan]

Thanks for the clarification. Below is some code that you can try. It assumes your lists of pairs is the only thing in Columns A:B and that your lookup parent name is in Cell C2. It will display the dependency list beginning at Cell C4. You could modify that to lookup a selected cell using a right-click menu macro as described in your OP.

Excel Workbook
	A	B	C
1	Parent	Child	Lookup Dependents of:
2	AZPHNX-DEVILS MOUNTAIN	AZPHNX-I-17 & KACHINA VILLAGE	AZPHNX-MOUNT ELDEN
3	AZPHNX-DEVILS MOUNTAIN	AZPHNX-VOLUNTEER MOUNTAIN
4	AZPHNX-VOLUNTEER MOUNTAIN	AZPHNX-I-17 & EXIT 236	Dependency List
5	AZPHNX-MOUNT ELDEN	AZPHNX-DEVILS MOUNTAIN	AZPHNX-DEVILS MOUNTAIN
6	AZPHNX-MOUNT ELDEN	AZPHNX-TWIN ARROWS	AZPHNX-I-17 & KACHINA VILLAGE
7			AZPHNX-VOLUNTEER MOUNTAIN
8			AZPHNX-I-17 & EXIT 236
9			AZPHNX-TWIN ARROWS
Sheet

Paste both the sub and function into a standard code module...

Sub MakeDependencyList()
    Dim vData As Variant, vMatches As Variant, vDependents As Variant
    Dim sMatch As String
    Dim i As Long, iMatchIdx As Long

    
    sMatch = Range("C2").Text 'cell with lookup parent ie: "AZPHNX-MOUNT ELDEN"

    
    vData = Range("A2:B" & Cells(Rows.Count, "A").End(xlUp).Row)

    
    ReDim vMatches(1 To UBound(vData))
    iMatchIdx = 0
    iMatchIdx = FindChildren(vData, vMatches, iMatchIdx, sMatch)

    
    '--display results
    With Range("C4")
        .Value = "Dependency List"
        If iMatchIdx = 0 Then
            .Cells(1) = "No Dependents"
        Else
            .Cells(2).Resize(iMatchIdx) = Application.Transpose(vMatches)
        End If
    End With


    '--Cleanup
    Erase vMatches
    Erase vData
End Sub


Private Function FindChildren(vData As Variant, vMatches As Variant, _
    iMatchIdx As Long, sMatch As String) As Long
    Dim i As Long
    Dim sChild As String

    
    For i = 1 To UBound(vData)
        If vData(i, 1) = sMatch Then
            sChild = vData(i, 2)
            '--Optional: display dependent pairs in Immediate Window
            Debug.Print sMatch & " --> " & sChild

 
            '--check if already processed this child to avoid endless loop
            If IsNumeric(Application.Match(sChild, vMatches, 0)) Then
                MsgBox "Duplicate child listing for: " & _
                    sChild, vbExclamation
            Else
                '--new match- add to dependents list
                iMatchIdx = iMatchIdx + 1
                vMatches(iMatchIdx) = sChild

                                   
                '--recursive call to find dependents of found child
                iMatchIdx = FindChildren(vData, vMatches, iMatchIdx, _
                    sMatch:=sChild)
            End If
        End If
    Next i
    FindChildren = iMatchIdx
End Function

To help with your debugging or further development, there is an optional statement that will display each dependent pair in the Immediate Window of the VBE. For the example above that looks like this:

AZPHNX-MOUNT ELDEN --> AZPHNX-DEVILS MOUNTAIN
AZPHNX-DEVILS MOUNTAIN --> AZPHNX-I-17 & KACHINA VILLAGE
AZPHNX-DEVILS MOUNTAIN --> AZPHNX-VOLUNTEER MOUNTAIN
AZPHNX-VOLUNTEER MOUNTAIN --> AZPHNX-I-17 & EXIT 236
AZPHNX-MOUNT ELDEN --> AZPHNX-TWIN ARROWS

 

[Jaimarti]

Thank you JS411 I will attemp to use it today and let you know how it works out.

 

[Jaimarti]

Just tried it and it worked perfect without any changes it did exactly what I needed. I have been trying to do this same thing for a while, Thank you.</SPAN>