Parent Child Hierarchy

[mikeyosyd]

Hi wonderful people. I'm hoping you may be able to help. Apologies if this has already been solved elsewhere.

I have HR data like the inout data table below where I have manager and subordinate employee numbers.

I'd like to find some VBA code to output the table to the right below. In my case of the HR hierarchy data this enables me to pick any manager and find all the subordinates to them down through the full organisational tree.


[TABLE="width: 435"]
<colgroup><col width="87" span="5" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="class: xl65, width: 87"]Input Data[/TD]
[TD="width: 87"][/TD]
[TD="width: 87"][/TD]
[TD="width: 87"]Output Data[/TD]
[TD="width: 87"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66, align: center"]Parent[/TD]
[TD="class: xl66, align: center"]Child[/TD]
[TD][/TD]
[TD="class: xl66, align: center"]Parent [/TD]
[TD="class: xl66, align: center"]Children[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 87"]A[/TD]
[TD="class: xl65, width: 87"]B[/TD]
[TD][/TD]
[TD="class: xl65, width: 87"]A[/TD]
[TD="class: xl65, width: 87"]B[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 87"]A[/TD]
[TD="class: xl65, width: 87"]C[/TD]
[TD][/TD]
[TD="class: xl65, width: 87"]A[/TD]
[TD="class: xl65, width: 87"]C[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 87"]C[/TD]
[TD="class: xl65, width: 87"]D[/TD]
[TD][/TD]
[TD="class: xl65, width: 87"]A[/TD]
[TD="class: xl65, width: 87"]D[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 87"]C[/TD]
[TD="class: xl65, width: 87"]E[/TD]
[TD][/TD]
[TD="class: xl65, width: 87"]A[/TD]
[TD="class: xl65, width: 87"]E[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 87"]D[/TD]
[TD="class: xl65, width: 87"]G[/TD]
[TD][/TD]
[TD="class: xl65, width: 87"]A[/TD]
[TD="class: xl65, width: 87"]G[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 87"]G[/TD]
[TD="class: xl65, width: 87"]H[/TD]
[TD][/TD]
[TD="class: xl65, width: 87"]A[/TD]
[TD="class: xl65, width: 87"]H[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl65, width: 87"]C[/TD]
[TD="class: xl65, width: 87"]D[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl65, width: 87"]C[/TD]
[TD="class: xl65, width: 87"]E[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl65, width: 87"]C[/TD]
[TD="class: xl65, width: 87"]G[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl65, width: 87"]C[/TD]
[TD="class: xl65, width: 87"]H[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl65, width: 87"]D[/TD]
[TD="class: xl65, width: 87"]G[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl65, width: 87"]D[/TD]
[TD="class: xl65, width: 87"]H[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl65, width: 87"]G[/TD]
[TD="class: xl65, width: 87"]H[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

See this code provided by pgc01 in another thread
https://www.mrexcel.com/forum/excel...-children-parent-post3892723.html#post3892723

Hope this helps

M.

 

[mikeyosyd]

See this code provided by pgc01 in another thread
https://www.mrexcel.com/forum/excel...-children-parent-post3892723.html#post3892723

Hope this helps

M.

Hi, thanks so much thats extremely promising. I got an error when I ran it on my dataset of "out of stack space". If you had any thoughts on how to resolve I'd be very grateful. As a guide, my dataset has around 7000 parent-child relationships on the input table. I'll post the same question back to the other thred.

Thanks

 

[Marcelo Branco]

I do not know if there is any limitation in Excel for recursive codes. I hope the code author can help you.

M.