PLEASE HELP ME OUT Parent-Child hierarchy construction

[_disha_]

Hi,

I am new to VB, saw ur post on providing parent child hierarchy in visually structured way in excel. Hoping that you will help me out in this issue . Please mail me in


ddhisha@gmail.com


There is a table in xl as below:
Calling Called
Folder1 Sub1
Folder1 Sub2
Sub1 Sub3
Sub3 Sub4
Folder2 Sub6
Folder2 Sub7
Sub7 Sub8
Sub7 Sub9


Explaination of the table:
"Folder1" is calling child "Sub1" and "Sub1" is calling "Sub3", and likewise ...


I need an output in an hierarchy saying parent->child->subchild...


Expected Output 1:
Folder1->Sub1->Sub3->Sub4
Folder1->Sub2
Folder2->Sub6
Folder2->Sub7->Sub8
Folder2->Sub7->Sub9


Expected Output 2:
In the 3rd column of excel against each row of Calling & Called columns ,Output should be as below(i.e, Parent|Child|...):
Folder1|Sub1
Folder1|Sub2
Folder1|Sub1|Sub3
Folder1|Sub1|Sub3|Sub4
Folder2|Sub6
Folder2|Sub7
Folder2|Sub7|Sub8
Folder2|Sub7|Sub9


Please help me out in this. Thanks

 

[_disha_]

Hi,

There is a DAX path() function available in excel 2013 ,as mentioned in the below link:
Understanding Functions for Parent-Child Hierarchies in DAX - Excel - Office.com
If you refer the PATH column , i need my output like this.

I installed Office 2013 trial version and strangely found that I cannot use this function.And there is no useful link of detailed explanation of using this function.
If there is any link where-in in VBA it is available as my expected output is ,please guide me. thanks ..

 

[mikerickson]

In your situation:
1) is there the possibility of a loop? like, Folder1>Sub1>Sub2>Folder1?

2) Do any children have more than one parent?

 

[_disha_]

Hi,

Kindly find the answers for your Qs:
1)No.. But there is a possibility of Folder1>Sub1>Sub2>Sub2 (i.e child can call itself)
2)Yes, Each parent can call the repeated child based on the row.
Example:
Folder1 Sub1
Folder1 Sub2
Sub1 Sub3
Sub3 Sub4
Folder2 Sub6
Folder2 Sub1

 

[mikerickson]

In your latest example, would
Folder2>Sub1>Sub3 be one of the output lines? or does one search only downward?

 

[_disha_]

It does search for Sub1 children >yes, Folder2>Sub1>Sub3 will be one of output line ,

 

[mikerickson]

Try this.
Add a class module, name the module clsLink and put this code in that class module

' in class module clsLink

Public Children As Collection
Public Name As String
Public IsChild As Boolean
Public Family As clsLinks

Function AddChild(ChildName As String) As clsLink
    Dim newChild As clsLink
    Set newChild = Family.AddLink(ChildName)
    On Error Resume Next
    Children.Add Item:=newChild, Key:=ChildName
    On Error GoTo 0
    Set AddChild = Children(ChildName)
    AddChild.IsChild = True
    Set newChild = Nothing
End Function

Private Sub Class_Initialize()
    Set Children = New Collection
End Sub

Private Sub Class_Terminate()
    Dim oneKid As clsLink
    For Each oneKid In Children
        Set oneKid = Nothing
    Next oneKid
    Set Children = Nothing
End Sub

Add another class module, name it clsLinks and put this code in.

' in class module clsLinks

Public Item As Collection

Function AddLink(Name As String) As clsLink
    Dim newLink As New clsLink
    With newLink
        .Name = Name
        Set .Family = Me
    End With
    On Error Resume Next
    Item.Add Item:=newLink, Key:=Name
    On Error GoTo 0
    Set AddLink = Item(Name)
    Set newLink = Nothing
End Function

Property Get Count() As Long
    Count = Item.Count
End Property

Private Sub Class_Initialize()
    Set Item = New Collection
End Sub

Private Sub Class_Terminate()
    Dim oneLink As clsLink
    For Each oneLink In Item
        Set oneLink = Nothing
    Next oneLink
    Set Item = Nothing
End Sub

Finally, in a normal module, put this code and run sub test().
You may have to adjust the definition of DataRange to suit your situation.

' in normal module

Sub test()
    Dim DataRange As Range
    Dim oneCell As Range
    Dim allLinks As New clsLinks, oneLink As clsLink
    Dim strResult As String, oneDString As Variant, dStrings As Object
    
    With Sheet1.Range("A:A")
        Set DataRange = Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
    End With
    DataRange.Offset(0, 2).Resize(65536, 10).ClearContents
    
    For Each oneCell In DataRange
        With allLinks.AddLink(CStr(oneCell.Value))
            .AddChild CStr(oneCell.Offset(0, 1).Value)
        End With
    Next oneCell
    For Each oneLink In allLinks.Item
        If oneLink.IsChild Then
        Else
            Set dStrings = DescentStrings(oneLink)
            For Each oneDString In dStrings
                Range("D65536").End(xlUp).Offset(1, 0).Value = oneDString
            Next oneDString
        End If
    Next oneLink
End Sub

Function DescentStrings(aParent As clsLink) As Object
    Dim Result As New Collection
    Dim oneKid As clsLink
    Dim oneName As String, oneDescent As Variant
    If aParent.Children.Count = 0 Then
        Result.Add Item:=aParent.Name
    Else
        For Each oneKid In aParent.Children
            For Each oneDescent In DescentStrings(oneKid)
                oneName = aParent.Name & ">" & oneDescent
                On Error Resume Next
                Result.Add Item:=oneName, Key:=oneName
                On Error GoTo 0
            Next oneDescent
        Next oneKid
    End If
    Set DescentStrings = Result
End Function

 

[_disha_]

Hi Mike,

INPUT:
Folder1 Sub1
Folder1 Sub2
Sub1 Sub3
Sub3 Sub4
Folder2 Sub6
Folder2 Sub7
Sub7 Sub8
Sub7 Sub9

I tried your code for the above input. Below is the output of your code:
OUTPUT
Folder1 >Sub2>
Sub1 >Sub3
Sub3 >Sub4>
Folder2 >Sub6>
Folder2 >Sub7
Sub7 >Sub8>
Sub7 >Sub9>



EXPECTED OUTPUT
Folder1 >Sub1>
Folder1 >Sub2>
Folder1 >Sub1 >Sub3
Folder1 >Sub1 >Sub3>Sub4>
Folder2 >Sub6>
Folder2 >Sub7
Folder2 >Sub7>Sub8>
Folder2 >Sub7>Sub9>


Please look into it and help.
Explanation of expected output:
In each result cell ,its parent hierarchy from root should be displayed.Say for example, for the input row 4th,Child is Sub4 ,its result cell should display "Folder1 >Sub1 >Sub3>Sub4>" i.e, parent of Sub3 which is Sub1,Sub1's parent, etc, till parent ends.

If I am unclear in explaining, please refer the link Understanding Functions for Parent-Child Hierarchies in DAX - Excel - Office.com
and see the PATH column :
CHILD PARENT PATH
112 112
14 112 112|14
3 14 112|14|3
11 3 112|14|3|11
13 3 112|14|3|13
162 3 112|14|3|162
117 162 112|14|3|162|117
221 162 112|14|3|162|221
81 162 112|14|3|162|81



Thanks.

 

[mikerickson]

My test file has this in A1:A9
Calling
Folder1
Folder1
Sub1
Sub3
Folder2
Folder2
Sub7
Sub7

this in B1:B9
Called
Sub1
Sub2
Sub3
Sub4
Sub6
Sub7
Sub8
Sub9

and (after running test) this result in D2 : D6
Folder1>Sub1>Sub3>Sub4
Folder1>Sub2
Folder2>Sub6
Folder2>Sub7>Sub8
Folder2>Sub7>Sub9

Is your input two columns or is is one column of space delimited strings?

 

[snb_]

crossposted here:

PLEASE HELP ME OUT Parent-Child hierarchy construction