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

 

[mikerickson]

Use the same MakeAllDec sub as in post #17.

 

[_disha_]

Mike,

Below is the code which i am running:

-------------------------------------------------
Sub test()
Dim allLinks As New clsLinks, oneLink As clsLink
Dim DataRange As Range, outPutRange As Range
Dim oneCell As Range
Dim strOfDescent As String, arrDescendants As Variant, arrOut As Variant
Dim i As Long

With Sheet1.Range("A:A")
Set DataRange = Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
End With
Set outPutRange = Range("F:F")

Rem make Parents and children
For Each oneCell In DataRange
With allLinks.AddLink(CStr(oneCell.Value))
.AddChild CStr(oneCell.Offset(0, 1).Value)
End With
Next oneCell

Rem write all descent strings
With outPutRange.EntireColumn
.Resize(, 3).ClearContents
For Each oneLink In allLinks.Item
If Not oneLink.IsChild Then
Call MakeAllDec(oneLink, "", .Cells)
End If
Next oneLink
Set outPutRange = Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
End With

For Each oneCell In outPutRange
strOfDescent = CStr(oneCell.Value)
arrDescendants = Split(strOfDescent, Delimiter)

For i = 1 To UBound(arrDescendants)
arrOut = arrDescendants
ReDim Preserve arrOut(0 To i)
strOfDescent = Join(arrOut, Delimiter)

If arrDescendants(i) <> LoopMarker Then
strOfDescent = Replace(strOfDescent, LoopMarker, vbNullString, 1, 1)
End If

With oneCell.Offset(0, 1).EntireColumn
If IsError(Application.Match(strOfDescent, .Cells, 0)) Then
With .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
.Value = strOfDescent
.Offset(0, 1).Value = i
End With
End If
End With
Next i
Next oneCell

With outPutRange.Offset(0, 1)
With Range(.EntireColumn.Cells(Rows.Count, 1).End(xlUp), .Cells(1, 1))
With .Resize(.Rows.Count, 2)
.Sort key1:=.Cells(1, 1), key2:=.Cells(1, 2)
End With
.Columns(2).ClearContents
End With
End With
End Sub
Function MakeAllDec(aParent As clsLink, Optional ByVal preString As String, Optional outCol As Range)
Dim StringOfDescent As String
Dim strOutputDescent As String
Dim oneKid As clsLink


StringOfDescent = preString & Delimiter & aParent.Name

If aParent.Children.Count = 0 Then
strOutputDescent = Mid(StringOfDescent, Len(Delimiter) + 1)
outCol.Cells(65536, 1).End(xlUp).Offset(1, 0) = strOutputDescent
Else
For Each oneKid In aParent.Children
If InStr(1, StringOfDescent & Delimiter, Delimiter & oneKid.Name & Delimiter, vbTextCompare) Then
strOutputDescent = StringOfDescent & Delimiter & LoopMarker
strOutputDescent = Replace(strOutputDescent, Delimiter & oneKid.Name & Delimiter, Delimiter & oneKid.Name & LoopMarker & Delimiter)
strOutputDescent = Mid(strOutputDescent, Len(Delimiter) + 1)
outCol.Cells(65536, 1).End(xlUp).Offset(1, 0) = strOutputDescent
Else
Call MakeAllDec(oneKid, StringOfDescent, outCol.EntireColumn)
End If
Next oneKid
End If
End Function

------------------------
O/p generated :


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

 

[kalyankumarganesang]

Hello Mike. Wanna first thank you for posting this. I tried this coding to create the hierarchy structure, but when loading a large data. I'm ended with error of Out of Stack space at DescentStrings function

to be precise, am locked at "For Each oneDescent In DescentStrings(oneKid)" this line item in the module. Hope you can educate me in overcoming this issue.

 

[kalyankumarganesang]

Hello Mike.


Want to first thank you for posting this. I tried this coding to create the hierarchy structure, but when loading a large data. I'm ended with error of Out of Stack space at DescentStrings function

to be precise, am locked at "For Each oneDescent In DescentStrings(oneKid)" this line item in the module. Hope you can educate me in overcoming this issue.
.