Family Tree - Parent-Child relationship - Derivation

Hi all,

am looking for some solution to derive multiple parent-child relationships up to nth level based on raw data.

i.e. multiple parent multiple children and even cross combinations. -- taking parent as the primary key or known starting points

I have taken explored over this forum and found few codes that do matches with my requirement but it only depicts linear relationship and not cross relationship (loopback relationship).

reference thread http://www.ozgrid.com/forum/showthread.php?t=158667

Also, I am facing some issues in executing the code taken from above thread as below -

Sub DisplayTree(ByVal sParent As String, dic As Dictionary, rOut As Range, _
    ByRef lRow As Long, ByVal lLevel As Long)

Error "User-defined type not defined"

forum.ozgrid.com/index.php?attachment/72649/
[ATTACH=CONFIG]72650[/ATTACH]

Am using Excel 2013 -- So some help here would be appreciated.

Dataset & code excel file is as attached. I am using exactly the same data first to check out the output as explained in reference thread.

Then modify it for Loopback relationship scenario.

Re: Family Tree - Parent-Child relationship - Derivation

Quote from MickG;793476

Perhaps this :-
code running from commandButton1

Code

Private Sub CommandButton1_Click()
    Dim rParents As Range, rNode As Range, rOut As Range
    Dim lRow As Long
     
    Set dic = CreateObject("scripting.dictionary")
    Set rParents = Range("A2", Range("A2").End(xlDown))
    Set rOut = Range("D2")
     
    For Each rNode In rParents
        If Not dic.Exists(rNode.Value) Then dic.Add rNode.Value, New Collection
        dic(rNode.Value).Add (rNode.Offset(, 1).Value)
    Next rNode
    Call DisplayTree("TableZ.Main", dic, rOut, lRow, 0)
End Sub
 
 ' Display the tree
Sub DisplayTree(ByVal sParent As String, dic, rOut As Range, _
    ByRef lRow As Long, ByVal lLevel As Long)
     
     
    Dim vChild
    If lLevel + lRow = 0 Then rOut = sParent
    For Each vChild In dic(sParent)
        lRow = lRow + 1
        rOut(lRow, lLevel + 2) = vChild
        If dic.Exists(vChild) Then
            lRow = lRow - 1 '<-----I added this
            Call DisplayTree(vChild, dic, rOut, lRow, lLevel + 1)
        End If
    Next vChild
End Sub

Display More

Hi MickG

Thanks a ton this did work.

Now coming on to my actual requirement -- am looking for loopback relationship

Schematic attached

The above code gives me the results -- but is it possible to remove the duplicate results and show an arrow or some line based output.

Just asking if not will have to run a lookup to match the loopback responses and assign color codification. (see snapshot for requirement understanding)

Also, have attached the updated excel for matching loopback relationship sample.

[ATTACH=CONFIG]72653[/ATTACH] forum.ozgrid.com/index.php?attachment/72654/

Thanks & Regards,
Venkat

Re: Family Tree - Parent-Child relationship - Derivation

Thanks sktneer for your response too. Understood the referencI i was missing out in the code.

Both of the response codes works as a cream for linear as well as Cross relationship based / non-linear relationships.

The only point left is to remove the duplicate outputs from the tree and represent it how they are related. am still figuring the right way to sort it.

You can check the attached docs for reference.

Thanks a lot
Regards,
Venkat

Re: Family Tree - Parent-Child relationship - Derivation

Hi All,

I tried to work on the loopback scenario with the same piece of Code and found out one thing --- If the loopback relationship scenario is related to any last node the code works great but if there is any loopback relationship scenario in the middle order the code breaks up as it goes into infinite loop of searching and getting the same results.

If somehow this can be controlled then it should reach my requirement.

For simplicity of understanding assume the Dataset as below:

[TABLE="width: 111"]

Parent

Child

A

B

B

C

C

D

C

E

E

B

E

F

F

B

F

G

[/TABLE]

Current Output

[TABLE="width: 433"]

A

B

C

D

E

B

C

D

E

B

C

D

E

B

C

D

E

B

C

D

E

B

C

D

E

B

C

D

E

B

C

D

[/TABLE]

Desired Output based
[TABLE="width: 216"]

A

B

C

D

E

B

F

B

G

[/TABLE]

[ATTACH=CONFIG]72668[/ATTACH]

Any suggestions here ??

Thanks & Regards,
Venkat