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]

I was just coming to post the same thing when I was looking at it this morning I actually found that same discrepancy. Thank you for looking and thank you for the help.</SPAN>

 

[chrisjaong]

Hi Jerry

This worked like a charm!

I was wondering, would it be possible to have all the results pasted into a single cell? i.e. Separated by a ~, Child1~Child2~Child3 etc.

Apologies if I appear to be a complete novice, I have just started learning VBA and macros!

I need to determine the multi level relationships for several hundred records and was thinking of running the macro as a loop until it hit the bottom of the list.

Any help would be much appreciated!!!

Thanks in advance,

Chris.

 

[Jerry Sullivan]

Hi Chris, Adapting the code above for the scenario you described in this thread is quite an accomplishment for someone just beginning with VBA.

http://www.mrexcel.com/forum/excel-...ro-determine-all-child-nodes-parent-node.html

It's better if we continue on that thread to make it less confusing for others to follow.
If you can post a screen shot like the ones above that show your starting point and desired result, I'll be glad to try to help.

 

[chrisjaong]

Hi Jerry,

I've solved it!

Some minor issues still but really close to finishing.

Thanks for all your help, you've saved me many days of heartache!

Would there be anyway to adapt your code to include the 'Parent' in the listed output?

Thanks!

Chris.

 

[chrisjaong]

Sorry, Ignore that post.

Managed to solve that as well!

Thanks again and looking forward to helping others if I can!

Cheers

 

[coldsteelmike]

Jerry!

First off; Nice work on this parent-child code!

I've been trying to modify the code to do a couple of things, but so far have been unsuccessful.

I'm trying to loop through the entire list of Parents instead of the single input
As well, I'd like to add to the right, the different "generations" of items in the list. So if a child has a child, the output in the immediate window would show:

Parent --> Child1 --> Child2 --> Childn
Parent --> Child1

Thanks in advance for any help you can provide.

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.

	A	B	C
Parent	Child
AZPHNX-DEVILS MOUNTAIN	AZPHNX-I-17 & KACHINA VILLAGE	AZPHNX-MOUNT ELDEN
AZPHNX-DEVILS MOUNTAIN	AZPHNX-VOLUNTEER MOUNTAIN
AZPHNX-VOLUNTEER MOUNTAIN	AZPHNX-I-17 & EXIT 236
AZPHNX-MOUNT ELDEN	AZPHNX-DEVILS MOUNTAIN	AZPHNX-DEVILS MOUNTAIN
AZPHNX-MOUNT ELDEN	AZPHNX-TWIN ARROWS	AZPHNX-I-17 & KACHINA VILLAGE
		AZPHNX-VOLUNTEER MOUNTAIN
		AZPHNX-I-17 & EXIT 236
		AZPHNX-TWIN ARROWS

<colgroup><col style="width:30px; "><col style="width:215px;"><col style="width:223px;"><col style="width:219px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #ccffff"]Lookup Dependents of:[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #ccffff"]Dependency List[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

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

 

[Jerry Sullivan]

I've been trying to modify the code to do a couple of things, but so far have been unsuccessful.

I'm trying to loop through the entire list of Parents instead of the single input
As well, I'd like to add to the right, the different "generations" of items in the list. So if a child has a child, the output in the immediate window would show:

Parent --> Child1 --> Child2 --> Childn
Parent --> Child1

Hi Mike, Sorry for my delay in responding to this. Could you post a small example that shows what your starting data might look like?

Please use one of the tools or methods described in the thread linked below that will allow others and me to copy-paste your example into a worksheet.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729

 

[coldsteelmike]

Jerry!

Thanks for the reply. The data is in the exact same format as the data in this thread, i.e. parents in A column, children in B column, with headers in row 1.

Per your request, I've attached an example.

 

[coldsteelmike]

Well apparently the attachment didn't take.

The data is in the exact same cells as post number 8 shows.

A	B	C
Parent	Child
AZPHNX-DEVILS MOUNTAIN	AZPHNX-I-17 & KACHINA VILLAGE	AZPHNX-MOUNT ELDEN
AZPHNX-DEVILS MOUNTAIN	AZPHNX-VOLUNTEER MOUNTAIN
AZPHNX-VOLUNTEER MOUNTAIN	AZPHNX-I-17 & EXIT 236
AZPHNX-MOUNT ELDEN	AZPHNX-DEVILS MOUNTAIN	AZPHNX-DEVILS MOUNTAIN
AZPHNX-MOUNT ELDEN	AZPHNX-TWIN ARROWS	AZPHNX-I-17 & KACHINA VILLAGE
		AZPHNX-VOLUNTEER MOUNTAIN
		AZPHNX-I-17 & EXIT 236
		AZPHNX-TWIN ARROWS

<tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

[TD="bgcolor: #CCFFFF"]Lookup Dependents of:[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]

[TD="bgcolor: #CCFFFF"]Dependency List[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]

[TD="bgcolor: #CACACA, align: center"]9[/TD]

</tbody>

What I'm looking for help with, is:

1. How to loop through the entire list of parents?...instead of the single input?
2. How to include all generations of a parent, separated by "-->" in the immediate window?

Again, thanks for any help you can provide.

Also, I still do not have the ability to edit posts, even though it states that I do in 'Posting Permissions'...the edit button is not there. Are there any tips or tricks with my account, or could it be my browser?

Thanks,

Mike

 

[Jerry Sullivan]

Mike,

I'm not sure of the best way to achieve the desired result you describe....

Parent --> Child1 --> Child2 --> Childn

...when there are multiple "siblings" or relationships other than just a single parent-child chain.

One approach that would remove some complexity from the problem, would be to have a separate row for each unique item in the dataset. That row would show the item, its Parent > Grand Parent > Great Grand Parent...

I'm assuming this task has the constraint that each Item can have only one Parent. Also, there can be no "illogical" scenarios such as a Item being the Parent of its Grand-Parent.

The result of this approach for the example might look like this:

Excel 2013
	A	B	C	D
1	Child	Parent1	Parent2	Parent3
2	AZPHNX-I-17 & KACHINA VILLAGE	AZPHNX-DEVILS MOUNTAIN	AZPHNX-MOUNT ELDEN
3	AZPHNX-VOLUNTEER MOUNTAIN	AZPHNX-DEVILS MOUNTAIN	AZPHNX-MOUNT ELDEN
4	AZPHNX-I-17 & EXIT 236	AZPHNX-VOLUNTEER MOUNTAIN	AZPHNX-DEVILS MOUNTAIN	AZPHNX-MOUNT ELDEN
5	AZPHNX-TWIN ARROWS	AZPHNX-MOUNT ELDEN
6	AZPHNX-MOUNT ELDEN
Sheet2

Would modified code that returned that result work for your needs?