Make a formula to design a hierarchy from a parent child relation

[jbesclapez]

Hello,

I get regularly files with parent-child hierarchy in a unsorted way. My task it to first, sort it the way it should be sorted and then on another place make a graphical hieararchy in excel as it is easier to read.
I would like to have a formula that helps me do this. The formula would select the range for parent, then select the range for child, then select a cell where I can copy paste the sorted result of the hierarchy and another cell that would would display the graphical hierarchy. The formula could be called ParentChild and then would look like this
=ParentChild(A6:A10;C6:C10,F5,P2)
A6:A10 are the array of the parent
C6:C10 are the array of the parent and an error should pop up if the array as a different size (each child as a parent except the member Root)
F5 is the place were we will sort a clean hierarchy. Note that the F5 to Fx will be for the parent when G5 to Gx will be for the Child. Optional :This array should have no data before.
P2 is the place where the array will be paste. So if the hiearchy is 3 level deep than the hierarchy will be shown in a array from P2 to Sx - As P is the root, Q is level 1, R is level 2, S is level 3. Optional :This array should have no data before.
Note:The value in the formula are not static! It is not always A6:A10 for example!


Note that the depth of the hierarchy can change and is not know before.

So, in real life you get this file:

Pizza	Margerita
Root	HotDog
Root	Pizza
Margerita	Cheese
Pizza	Sea
Sea	Tuna
Burger	Bun
HotDog	Sausage
Sea	Shrimp
Root	Sandwich
Sandwich	Burger
Burger	Steack
Burger	Tomato
Burger	Cucumber
HotDog	Bun
Root	Offer
Offer	Pizza
Offer	Sandwich
Margerita	Tomato
Margerita	Oliva
Sea	Tomato
Sea	Cheese

Then it gets nicely sorted like this :

Root	Pizza
Pizza	Margerita
Margerita	Tomato
Margerita	Oliva
Margerita	Cheese
Pizza	Sea
Sea	Tuna
Sea	Shrimp
Sea	Tomato
Sea	Cheese
Root	Sandwich
Sandwich	Burger
Burger	Steack
Burger	Bun
Burger	Tomato
Burger	Cucumber
Root	HotDog
HotDog	Sausage
HotDog	Bun
Root	Offer
Offer	Pizza
Offer	Sandwich

And it creates the hierarchy like this : Optional: It should display graphically in excel like in the picture attached.

Root	Pizza
		Margerita
			Tomato
			Oliva
			Cheese
		Sea
			Tuna
			Shrimp
			Tomato
			Cheese
	Sandwich
		Burger
			Steack
			Bun
			Tomato
			Cucumber
		HotDog
			Sausage
			Bun
	Offer
		Pizza
		Sandwich

Thanks for your time and effort,

Have a good day,

 

[jbesclapez]

I forgot to add that a child can have n Parent and therefore be in many hiearchies... Mike?Fluff? Please? This is to high level for me as I am sure you will be using dictionnaries no?

 

[jbesclapez]

OK, I found this VBA that nearly does the job but it needs changes. In this VB the child is in column A and the Parent in B. And it is not a formula and does not ask for any place where to store the data...
WOrkign on it but help would be appreciated

Dim n, ligne, debOrg, Tbl()
Sub organigramme()
  Tbl = Range("A2:B" & [A65000].End(xlUp).Row).Value
  Set debOrg = [d8]
  debOrg.Resize(25, 25).Clear
  n = UBound(Tbl)
  ligne = 0: Ecrit Tbl(1, 1), 1
  ligne = 0: Présentation Tbl(1, 1), 1
End Sub
Sub Ecrit(parent, niv)       ' procédure récursive
  ligne = ligne + 1
  debOrg.Offset(ligne, niv) = parent
  debOrg.Offset(ligne, niv).Borders(xlEdgeLeft).Weight = xlThin
  debOrg.Offset(ligne, niv).Borders(xlEdgeBottom).Weight = xlThin
  For i = 1 To n
    If Tbl(i, 2) = parent Then Ecrit Tbl(i, 1), niv + 1
  Next i
End Sub
Sub Présentation(parent, niv) ' procédure récursive
  ligne = ligne + 1
  Fin = debOrg.Offset(ligne, niv).End(xlDown).Row
  If Fin < 100 Then
    For i = ligne To Fin - debOrg.Row
       debOrg.Offset(i, niv).Borders(xlEdgeLeft).Weight = xlThin
    Next i
  End If
  For i = 1 To n
    If Tbl(i, 2) = parent Then Présentation Tbl(i, 1), niv + 1
  Next i
End Sub

 

[chrispaulpearce]

also struggling with this exact issue, any luck?
in the way I'm viewing it's the same principle, except I have an employee name and a manager name, I want to create a hierarchy of this data

 

[annma83]

I am struggling a lot with this. Did you happen to find the VBA code corrections you could share? Or an example spreadsheet would be even better if available?