Build parent/child hierarchy

[adygelber]

Hello,

I kindly ask for your support because I have to achieve something that seems to be simple but I just can't figure it out how to do it.

Mainly, I have two columns. The first one contains some parent codes, the second one contains some child codes which can consequently become parent codes for other child codes. And so on.

Having this in mind, I want to create a hierarchy like the one from the below example. The example file was created manually with a minimum number of records but the final database can be way bigger.

Parent	Child
AB00000	AB00001		AB00000	AB00001	AB00002	AB00005	AB00009	AB00010
AB00001	AB00002					AB00006
AB00001	AB00003					AB00005
AB00001	AB00004					AB00007
AB00002	AB00005					AB00005
AB00002	AB00006				AB00003	AB00007
AB00002	AB00005				AB00004	AB00008
AB00002	AB00007
AB00002	AB00005
AB00003	AB00007
AB00004	AB00008
AB00005	AB00009
AB00009	AB00010

<colgroup><col style="width:48pt" span="9" width="64"> </colgroup><tbody>
</tbody>

Thank you in advance for your support!
A.B.

Also posted here:
[url]http://www.vbaexpress.com/forum/showthread.php?57247-Build-parent-child-hierarchy
Build parent/child hierarchy
[/URL]

 

[mikerickson]

What is the end goal of this? Is it other than the display question that you asked.

Is it the case that every person has only one parent?
Is it the case that everyone is descended from a single "great-grandmother" (AB00000 in the OP)?

 

[adygelber]

Please excuse my late reply.
As I anticipated, there was a problem with my database. Some values were doubled and appeared as parent for themselves.
Now I fixed the database and works perfectly.

I thank you again for your time and availability,
A.B.

 

[adygelber]

[SOLVED] Build parent/child hierarchy

Solved!

 

[adygelber]

Re: [SOLVED] Build parent/child hierarchy

Only when you have some time to spare, I would really like to understand a little bit one thing that I saw you have used in your script.

You declared

ByVal aPerson As String and ByRef WriteTo As Range

but I can't understand how excel knows what value to assign to these variables?
First time when you mention aPerson is here

If LCase(oneCell.Value) = LCase(aPerson) Then

but never before a value has been assigned to the aPerson.

It is clear that I don't understand how it works ByRef/ByVal but reading some content on MSDN I wasn't really enlightened.

Thank you Mike and please excuse me if I am asking too much...

 

[mikerickson]

Re: [SOLVED] Build parent/child hierarchy

That is the declaration of the arguments of WriteDownFrom.

WriteDownFrom writes the name of the indicated person (aPerson) in the indicated cell (WriteTo) and all the descendents of aPerson.

The ByRef is there so that the routine can return the location of where aPerson's sister should be written. That may be one or many rows below the cell where aPerson is written.


That second line is part of the loop through the data base, looking for the children of aPerson. The LCase is there to make the test case insensitive.

 

[adygelber]

Re: [SOLVED] Build parent/child hierarchy

WriteDownFrom writes the name of the indicated person (aPerson)

This is where I've lost myself
You say "the indicated person" but where is indicated (defined) aPerson? I only see the declaration of the variable as "ByVal aPerson As String" but where exactly you assign a value to the aPerson?

I know that probably sounds very stupid this question but I used to always declare variables as something like aPerson = "Something" and now I see that aPerson gets some values but I don't see where the values are assigned to it.

Hope that I am not bothering you...I just try to understand a concept in order to grow myself up

 

[adygelber]

Re: [SOLVED] Build parent/child hierarchy

Sorry for the stupid question. I finally understood
Thanks!

 

[Nemesis21]

Code works perfectly fine until it should return next parent and its children. How to loop it to start all over and show next set of data?

This refunded version doesn't need class modules

Sub test()
    WriteDownFrom CStr(ParentDataRange.Cells(1, 1)), Range("G1")
End Sub

Sub WriteDownFrom(ByVal aPerson As String, ByRef WriteTo As Range)
    Dim Children As Collection
    Dim oneCell As Range
    Dim i As Long
    
    Set Children = New Collection
    For Each oneCell In ParentDataRange.Columns(1).Cells
        If LCase(oneCell.Value) = LCase(aPerson) Then
            On Error Resume Next
                Children.Add Item:=oneCell.Offset(0, 1), Key:=CStr(oneCell.Offset(0, 1))
            On Error GoTo 0
        End If
    Next oneCell
    
    WriteTo.Value = aPerson
    
    If Children.Count = 0 Then
        Set WriteTo = WriteTo.Offset(1, 0)
    Else
        Set WriteTo = WriteTo.Offset(0, 1)
        For i = 1 To Children.Count
            WriteDownFrom Children(i), WriteTo
        Next i
        Set WriteTo = WriteTo.Offset(0, -1)
    End If
    
End Sub

Function ParentDataRange() As Range
    With Sheet1.Range("A:A")
        Set ParentDataRange = Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
    End With
    Set ParentDataRange = ParentDataRange.Resize(, 2)
End Function

 

[ShaneGossard]

Mike,
Thanks for posting the above code. I do have a small variation to the original post!!

I actually have a 3 part parent child relationship (hierarchy). I have had to concatenate into a unique identifier to use the proposed solution.....makes reporting and slicing data tough. Do you have a method to handle data in the below format?

I have a List of about 10K different sales orders that are nested with the sales order (P) being the grand-parent, the Parent Sales Order Line Item Identifier (Q) obviously being the parent, and Sales Order Line Item Identifier (R) being the child of the parent. Each sales order can have multiple parents with multiple children and each child could also be a parent of multiple children. The longest path length has been around 6 but would love the capability to go to 10 levels. I have been able to get PowerBI to solve this, but the amount of calculating I subsequently have had to code makes the dashboard too clunky to really be of value to my users. Would love to do all the calculations in excel, but i can't figure out how to develop the parent child relationship hierarchy's.

Appreciate any help you could provide with this!!