excelvbaexcel-formulaexcel-365

Flatten Parent Child Hierarchy in Excel (using formula or VBA)

I want to flatten Parent Child Hierarchy in Excel. For example:

enter image description here

If my original data is in blue, the destination I want to change is the orange area. How can I use VBA or Excel formula to resolve it?

Solution

  • Probably less efficient than Jos' version, but I haven't been able to test that yet (not behind a laptop).

    This version is solely formula:

    =DROP(
      REDUCE("",
             SORT(
                  REDUCE("",
                         MAP(UNIQUE(TOCOL(Table3)),
                         LAMBDA(x,
                                REDUCE(x,SEQUENCE(ROWS(UNIQUE(Table3[Col 1]))+1),
                                LAMBDA(a,b,                                
                                       TEXTJOIN("|",1,a,XLOOKUP(TEXTAFTER(a,"|",-1,,,a),                                  
                                                                Table3[Col 2],                                  
                                                                Table3[Col 1],                                                    
                                                                "")))))),
                  LAMBDA(a,b,                     
                     LET(c,TEXTSPLIT(b,"|"),
                         d,COUNTA(c),
                         VSTACK(a,TEXTJOIN("|",1,INDEX(c,,SEQUENCE(,d,d,-1)))))))),
      LAMBDA(a,b,
         LET(c,TEXTSPLIT(b,"|"),
             d,COUNTA(c),
             IFERROR(
                     VSTACK(a,
                            IF(SEQUENCE(,d)=d,INDEX(c,,d),"")),
                     "")))),
      2)

    enter image description here

    It takes all unique values from the table (Table3 in my example) and iterates from looking up the value in the child column Col 2 and return it's parent Col 1 and look that result up until it can't find any parent to the value.

    This results in a chain from child up to the master parent. To be able to sort the results in a proper manner, this result needs reversed (master parent to latest child) and sort this. Than only show the last value per row.

    I hope it helps.