• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Recursive Lambda to join full parent-child hierarchy

Marcelo Machado

Marcelo Machado

New Member
Hi. I'd like a result like last column in the picture (Full Hierarchy).
I can do that creating multiple support columns. But I'm almost sure that it's possible to do that using recursive lambda.
I made some tentatives, but no success.
Can anyone help on that?
Tks!

col A = child info
col B - parent info
col c to k = xlookup to search the parent of the previous parent
then ... col L = join results into one column ... starting from the top level to the row child

hierarchy problem.png
 
Hello Marc

You can achieve this with a recursive lambda function in Excel. Here's a simplified version of how you can implement it:

Code: 
=LET(

    hierarchy, A2:A10,  // Column A contains child info

    parentList, B2:B10, // Column B contains parent info

    joinHierarchy, LAMBDA(child, IF(ISERROR(child), "", 

        LET(

            parentIndex, MATCH(child, hierarchy, 0),

            parent, INDEX(parentList, parentIndex),

            IF(parent="", child, joinHierarchy(parent) & " > " & child)

        )

    )),

    joinHierarchy(A2)

)
 
Hey Monty.
The suggestion didn't work. Take a look below.
Also, it took some seconds to calculate it. I don't know if it was time/process consuming due to the errorm or if this solution is really tuff .

hierarchy problem2.png
 
Monty has the right idea but the syntax doesn't work. That's because the name joinHierarchy can't be used in the name_value parameter that defines it. There is a trick to making this work. We have to define the function as a parameter to the LAMBDA function:

Code: 
=LET(
  ChildList, $A$1:$A$10,
  ParentList, $B$1:$B$10,
  BuildHierarchy,
    LAMBDA(BuildHierarchyParameter,Child,
      LET(
        Parent, INDEX(ParentList, MATCH(Child, ChildList, 0)),
        IF(OR(LEN(Parent) = 0, ISNA(Parent)), Child, BuildHierarchyParameter(BuildHierarchyParameter, Parent) & " > " & Child)
      )
    ),
  BuildHierarchy(BuildHierarchy, A1)
)

What we are doing here is building the LAMBDA function call using a prototype of itself so-to-speak and then passing the actual function - BuildHierarchy in this case - to the BuildHierarchy function.

Kevin
 
Just for fun, this version of the formula creates the parent child hierarchy and places it in multiple cells, each cell containing a parent or child.

Code: 
=TEXTSPLIT(
  LET(
    ChildList, $A$1:$A$10,
    ParentList, $B$1:$B$10,
    BuildHierarchy,
      LAMBDA(F,Child,
        LET(
          Parent, INDEX(ParentList, MATCH(Child, ChildList, 0)),
          IF(OR(LEN(Parent) = 0, ISNA(Parent)), Child, F(F, Parent) & "|" & Child)
        )
      ),
    BuildHierarchy(BuildHierarchy, A1)
  ),
"|")

Kevin
 
You must log in or register to reply here.
Back
Top