Org Chart - Using a Macro to Determine all Child Nodes for a Parent Node

[chrisjaong]

Hi all!

This is my first time posting here but have found many useful things from this forum.

I need to determine all the Child Nodes for each Node within a Business Department Hierarchy.

I have a list in Excel with all the nodes and their one up parent node

i.e.

DEP1

DEP2 DEP 3

DEP 4 DEP 5 DEP 6 DEP 7

[TABLE="width: 500"]
<tbody>[TR]
[TD]Node[/TD]
[TD]Parent[/TD]
[/TR]
[TR]
[TD]DEP1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DEP2[/TD]
[TD]DEP1[/TD]
[/TR]
[TR]
[TD]DEP3[/TD]
[TD]DEP1[/TD]
[/TR]
[TR]
[TD]DEP4[/TD]
[TD]DEP2[/TD]
[/TR]
[TR]
[TD]DEP5[/TD]
[TD]DEP2[/TD]
[/TR]
[TR]
[TD]DEP6[/TD]
[TD]DEP3[/TD]
[/TR]
[TR]
[TD]DEP7[/TD]
[TD]DEP4[/TD]
[/TR]
</tbody>[/TABLE]

Now this is just an example, in reality it goes down 7 levels with 315 nodes.

What I need is a list to show:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Node[/TD]
[TD]Child[/TD]
[/TR]
[TR]
[TD]DEP1[/TD]
[TD]DEP2~DEP3~DEP4~DEP5~DEP6~DEP7[/TD]
[/TR]
[TR]
[TD]DEP2[/TD]
[TD]DEP4~DEP5[/TD]
[/TR]
[TR]
[TD]DEP3[/TD]
[TD]DEP6~DEP7[/TD]
[/TR]
</tbody>[/TABLE]

and so on.

Can anyone think of a macro to be able to do this in excel?

As this will be used as data for a system, it will have to be calculated and determined in excel.

Really appreciate any form of advice from the Excel Guru's here!!!

Much thanks,

Chris.

 

[Jerry Sullivan]

Hi Chris and Welcome to MrExcel,

This thread has some code which you might be able to adapt.
http://www.mrexcel.com/forum/excel-questions/690748-multi-level-relationship-challenge.html

 

[chrisjaong]

Thanks Jerry!

Much appreciated. Tried to have a search on the forum but I guess my keywords were off.

I'll have a look and try my best!

Chris.

Hi Chris and Welcome to MrExcel,

This thread has some code which you might be able to adapt.
http://www.mrexcel.com/forum/excel-questions/690748-multi-level-relationship-challenge.html