Parent Child hierarchical format using Level

[tandv]

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

Node		Level
A100		1
101	Child 1	2
102	Child 2	3
103	Child 3	4
104	Child 4	5
A200		1
201	Child 1	2
202	Child 2	3
203	Child 3	4

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

And output will be

A100
	101	Child 1
		102	Child 2
			103	Child 3
				104	Child 4
A200
	201	Child 1
		202	Child 2
			203	Child 3

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

 

[MickG]

Try this for results in sheet 2.

[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

 

[tandv]

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)

 

[Fluff]

The sheet with your data needs to be the active sheet when you run the code

 

[tandv]

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

Node		Level
A100		1
101	Child 1	2
102	Child 2	3
103	Child 3	4
104	Child 4	5
105	Child 5	5
106	Child 6	5
A200		1
201	Child 1	2
202	Child 2	3
203	Child 3	4

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

Expected output

A100
	101	Child 1
		102	Child 2
			103	Child 3
				104	Child 4
				105	Child 5
				106	Child 6
A200
	201	Child 1
		202	Child 2
			203	Child 3

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

however it gives as below

A100
	101	Child 1
		102	Child 2
			103	Child 3
				104	Child 4
					105	Child 5
						106	Child 6
A200
	201	Child 1
		202	Child 2
			203	Child 3

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

 

[Peter_SSs]

.. when there are one child at each level however if there are multiple children at same level then it didn;t give correct.

Try this then

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
	A	B	C	D	E	F	G	H	I	J
1	Node		Level
2	A100		1		A100
3	101	Child 1	2			101	Child 1
4	102	Child 2	3				102	Child 2
5	103	Child 3	4					103	Child 3
6	104	Child 4	5						104	Child 4
7	105	Child 5	5						105	Child 5
8	106	Child 6	5						106	Child 6
9	A200		1		A200
10	201	Child 1	2			201	Child 1
11	202	Child 2	3				202	Child 2
12	203	Child 3	4					203	Child 3
Sheet1

 

[Peter_SSs]

Note that this result can also be achieved by formulas. Formula shown in E2 is copied across and down.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Node		Level
2	A100		1		A100
3	101	Child 1	2			101	Child 1
4	102	Child 2	3				102	Child 2
5	103	Child 3	4					103	Child 3
6	104	Child 4	5						104	Child 4
7	105	Child 5	5						105	Child 5
8	106	Child 6	5						106	Child 6
9	A200		1		A200
10	201	Child 1	2			201	Child 1
11	202	Child 2	3				202	Child 2
12	203	Child 3	4					203	Child 3
Sheet3