excelvbaexcel-formulaexcel-365# Flatten Parent Child Hierarchy in Excel (using formula or VBA)

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

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)
```

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.

- Build vertical summary from Columns with Count
- Excel - Create a Unit Conversion sheet - Multiple links
- update cell when column header matches a list
- Delete text in cell subject to Worksheet_Change
- Counting the number of visible rows after autofilter
- Excel 2007: AVERAGEIF, SUMIF, COUNTIF, MAXIF, MINIF across multiple sheets, multple rows
- Import CSV data from a txt file skipping the first line and adding headers
- Simplifying SumIFs formulas for efficient excel formula
- I'd like to find out how to find the current streak of non-negative numbers in a row of data in Excel
- ValueError: Invalid character found in sheet title
- decrypt excel files
- Excel table search funcion that looks for partial string match
- Combine macros to filter on today's and tomorrow's date
- Converting line breaks to commas in excel sheet using Powershell
- Combinations of numbers arranged side by side as many as a random number (VBA)
- How can I remove ONLY leading and trailing spaces while leaving spaces in between words alone with an excel formula?
- VBA Date as integer
- Sliding Window Auto Increment Range
- Count cells with different conditional ranges
- Turning flattened pivots data into tables -Error tables can't overlap - Excel Vba
- How do I get only a specific part of a cell in Excel or Numbers?
- Calculate the Legendre symbol of two integers in Excel
- Unable to get text wrapping or vertical centering to work with xlsxwriter
- Number stored as text warning in excel using POI
- VBA Excel paste to the columns in regular intervals
- Convert filtered values from formulas to values
- Sorting Dictionary by nested class objects data [VBA]
- Events does not appear when making an Outlook query from Excel
- The script works well when I use MSXML2.XMLHTTP.6.0, but it fails miserably when I switch to MSXML2.serverXMLHTTP.6.0
- How to convert text string (d h m s) to time format in excel