Search code examples
excelrecursionexcel-formulalambdaparent-child

Recursive Lambda to join full parent-child hierarchy

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? Thanks!

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

enter image description here

Child   Parent
1578123  
10000176    1578123
10000623    10000176
1584885     10000623
1606217     1584885
44000026    10000623
81003755    10000623
1579066     10000176
1603212     1579066
81014694    1603212
1603645     1579066
59000184    1579066
81011826    59000184
81017364    59000184
81014130    1579066
81020541    1579066
81020078    81020541
Solution

  • Using REDUCE:

    ="> "&IF([@Parent]="",[@Child],TEXTJOIN(" > ",TRUE,REDUCE([@Parent],SEQUENCE(10),LAMBDA(x,y,VSTACK(XLOOKUP(TAKE(x,1),[Child],[Parent],""),x)))))

    enter image description here

    NOTE: This does make an assumption that there are no more than 10 parents. If that is too small change the 10 to what you feel comfortable.

    To show that it works with text:

    enter image description here