Parent Child hierarchical format using Level

T

tandv

New Member
Joined
Dec 25, 2014
Messages
9
Hi,
I am trying to establish below in hierarchical format. Is there anyway if we can achieve output as below. tried grouping but didn't work.
Input
[TABLE="width: 192"]
<colgroup><col width="64" style="width:48pt" span="3"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Node[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"]Level[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A100[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]101[/TD]
[TD="bgcolor: transparent"]Child 1[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]102[/TD]
[TD="bgcolor: transparent"]Child 2[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]103[/TD]
[TD="bgcolor: transparent"]Child 3[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]104[/TD]
[TD="bgcolor: transparent"]Child 4[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A200[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]201[/TD]
[TD="bgcolor: transparent"]Child 1[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]202[/TD]
[TD="bgcolor: transparent"]Child 2[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]203[/TD]
[TD="bgcolor: transparent"]Child 3[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]


And output will be
[TABLE="width: 384"]
<colgroup><col width="64" style="width:48pt" span="6"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]A100[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]101[/TD]
[TD="bgcolor: transparent"]Child 1[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]102[/TD]
[TD="bgcolor: transparent"]Child 2[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]103[/TD]
[TD="bgcolor: transparent"]Child 3[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]104[/TD]
[TD="bgcolor: transparent"]Child 4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A200[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]201[/TD]
[TD="bgcolor: transparent"]Child 1[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]202[/TD]
[TD="bgcolor: transparent"]Child 2[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]203[/TD]
[TD="bgcolor: transparent"]Child 3[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Try this for results in sheet 2.
Code: 
[COLOR=navy]Sub[/COLOR] MG10Oct38
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, c [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Ac [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] R [COLOR=navy]As[/COLOR] Range, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range("B:B").SpecialCells(xlCellTypeConstants)
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng.Areas
[COLOR=navy]Set[/COLOR] Dn = Dn.Offset(-1, -1).Resize(Dn.Count + 1)
    Ac = 0
        [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] R [COLOR=navy]In[/COLOR] Dn
            c = c + 1: Ac = Ac + 1
              [COLOR=navy]With[/COLOR] Sheets("Sheet2")
                [COLOR=navy]If[/COLOR] c = 1 [COLOR=navy]Then[/COLOR]
                    .Cells(c, Ac) = R.Value
                [COLOR=navy]Else[/COLOR]
                    .Cells(c, Ac).Resize(, 2).Value = R.Resize(, 2).Value
                [COLOR=navy]End[/COLOR] If
             [COLOR=navy]End[/COLOR] With
        [COLOR=navy]Next[/COLOR] R
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
 
Last edited:
Upvote 0
Thanks Mick.
I tried to run code on Sheet 2 but it gives me run time error '1004' . Seems it can't find SpecialCells(xlCellTypeConstants)
 
Upvote 0
The sheet with your data needs to be the active sheet when you run the code
 
Upvote 0
Thanks. It works correctly when there are one child at each level however if there are multiple children at same level then it didn;t give correct. please refer below
[TABLE="width: 192"]
<colgroup><col width="64" style="width:48pt" span="3"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Node[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"]Level[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A100[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]101[/TD]
[TD="bgcolor: transparent"]Child 1[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]102[/TD]
[TD="bgcolor: transparent"]Child 2[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]103[/TD]
[TD="bgcolor: transparent"]Child 3[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]104[/TD]
[TD="bgcolor: transparent"]Child 4[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]105[/TD]
[TD="bgcolor: transparent"]Child 5[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]106[/TD]
[TD="bgcolor: transparent"]Child 6[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A200[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]201[/TD]
[TD="bgcolor: transparent"]Child 1[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]202[/TD]
[TD="bgcolor: transparent"]Child 2[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]203[/TD]
[TD="bgcolor: transparent"]Child 3[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]

Expected output
[TABLE="width: 384"]
<colgroup><col width="64" style="width:48pt" span="6"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]A100[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]101[/TD]
[TD="bgcolor: transparent"]Child 1[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]102[/TD]
[TD="bgcolor: transparent"]Child 2[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]103[/TD]
[TD="bgcolor: transparent"]Child 3[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]104[/TD]
[TD="bgcolor: transparent"]Child 4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]105[/TD]
[TD="bgcolor: transparent"]Child 5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]106[/TD]
[TD="bgcolor: transparent"]Child 6[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A200[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]201[/TD]
[TD="bgcolor: transparent"]Child 1[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]202[/TD]
[TD="bgcolor: transparent"]Child 2[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]203[/TD]
[TD="bgcolor: transparent"]Child 3[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

however it gives as below

[TABLE="width: 512"]
<colgroup><col width="64" style="width:48pt" span="8"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]A100[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]101[/TD]
[TD="bgcolor: transparent"]Child 1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]102[/TD]
[TD="bgcolor: transparent"]Child 2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]103[/TD]
[TD="bgcolor: transparent"]Child 3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]104[/TD]
[TD="bgcolor: transparent"]Child 4[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]105[/TD]
[TD="bgcolor: transparent"]Child 5[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]106[/TD]
[TD="bgcolor: transparent"]Child 6[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A200[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]201[/TD]
[TD="bgcolor: transparent"]Child 1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]202[/TD]
[TD="bgcolor: transparent"]Child 2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]203[/TD]
[TD="bgcolor: transparent"]Child 3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]

[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
tandv said:
.. when there are one child at each level however if there are multiple children at same level then it didn;t give correct.
Click to expand...
Try this then
Code: 
Sub Hierarchy()  Dim c As Range
  
  Application.ScreenUpdating = False
  For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
      c.Offset(, 3 + c.Offset(, 2).Value).Resize(, 2).Value = c.Resize(, 2).Value
  Next c
  Application.ScreenUpdating = True
End Sub

My sample data in A:C, results of code in E:J


Excel 2016
ABCDEFGHIJ
1NodeLevel
2A1001A100
3101Child 12101Child 1
4102Child 23102Child 2
5103Child 34103Child 3
6104Child 45104Child 4
7105Child 55105Child 5
8106Child 65106Child 6
9A2001A200
10201Child 12201Child 1
11202Child 23202Child 2
12203Child 34203Child 3
Sheet1
 
Upvote 0
Note that this result can also be achieved by formulas. Formula shown in E2 is copied across and down.

Excel Workbook
ABCDEFGHIJ
1NodeLevel
2A1001A100
3101Child 12101Child 1
4102Child 23102Child 2
5103Child 34103Child 3
6104Child 45104Child 4
7105Child 55105Child 5
8106Child 65106Child 6
9A2001A200
10201Child 12201Child 1
11202Child 23202Child 2
12203Child 34203Child 3
Sheet3
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
VBA code clean up
Replies
9
Views
482
mumps
mumps
S
  • Solved
How to match and sum multiple cells
Replies
3
Views
296
Fluff
Fluff
T
  • Question Question
Dynamically creating a parent-child table from anarra
Replies
0
Views
627
thegamerulez
T
G
  • Solved
Split items into Rows
Replies
2
Views
515
GreyFox8991
G
R
  • Solved
Alternative to using Aggregate
Replies
8
Views
335
shaowu459
shaowu459

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top