Forum Discussion
yogi_new
Jan 08, 2024Copper Contributor
Help on data for preparing relationship mapping between two columns
Hello Experts,
Greetings for the day!
I've below scenario in excel where I need help/guidance from you in getting the solution.
In below table, I've table number given in first column. The tables are classified in 2 categories viz, 'Parent' & 'Child', which is given in second column. In third column I've the parent table given for a child table on basis of which this child table is dependent.
Given Input:
Heading | Category | Dependent Heading |
Table 1 | PARENT | |
Table 2 | PARENT | |
Table 3 | PARENT | |
Table 4 | CHILD | Table 3 |
Table 5 | PARENT | |
Table 6 | CHILD | Table 5 |
Table 7 | CHILD | Table 5 |
Table 8 | PARENT | |
Table 9 | CHILD | Table 8 |
Table 10 | CHILD | Table 8 |
Table 11 | CHILD | Table 8 |
Table 12 | PARENT | |
Table 13 | PARENT | |
Table 14 | PARENT | |
Table 15 | CHILD | Table 14 |
Table 16 | CHILD | Table 14 |
I need to have the data mapping done in below listed format.
Expected Output:
Parent | Child 1 | Child 2 | Child 3 |
Table 1 | |||
Table 2 | |||
Table 3 | Table 4 | ||
Table 5 | Table 6 | Table 7 | |
Table 8 | Table 9 | Table 10 | Table 11 |
Table 12 | |||
Table 13 | |||
Table 14 | Table 15 | Table 16 |
Can you help in giving macro/formula to get the desired output?
You help is very appriciated.
Hi yogi_new
With Excel 365/Web:
=LET( Parents, FILTER(Table[Heading], Table[Category]="parent"), HasChild, UNIQUE( FILTER(Table[Dependent Heading], Table[Dependent Heading]<>"") ), Mapped, DROP( REDUCE("", HasChild, LAMBDA(seed,v, VSTACK(seed, TEXTJOIN(",",,v,FILTER(Table[Heading],Table[Dependent Heading]=v)))) ), 1 ), NoChild, FILTER(Parents, ISNA(XMATCH(Parents,HasChild))), Splitted, TEXTSPLIT( TEXTJOIN(";",, TEXTJOIN(";",,NoChild), TEXTJOIN(";",,Mapped)), ",", ";",,,"" ), Sorted, SORTBY(Splitted, XMATCH(CHOOSECOLS(Splitted,1),Parents)), VSTACK( HSTACK("Parent", "Child " & SEQUENCE(,COLUMNS(Splitted)-1)), Sorted ) )
- LorenzoSilver Contributor
On reflection... it's actually the same need/requirement as How to filter and get data from multiple rows for matching criteria so the same approach/formula can be re-used. See in attached file
- LorenzoSilver Contributor
Hi yogi_new
With Excel 365/Web:
=LET( Parents, FILTER(Table[Heading], Table[Category]="parent"), HasChild, UNIQUE( FILTER(Table[Dependent Heading], Table[Dependent Heading]<>"") ), Mapped, DROP( REDUCE("", HasChild, LAMBDA(seed,v, VSTACK(seed, TEXTJOIN(",",,v,FILTER(Table[Heading],Table[Dependent Heading]=v)))) ), 1 ), NoChild, FILTER(Parents, ISNA(XMATCH(Parents,HasChild))), Splitted, TEXTSPLIT( TEXTJOIN(";",, TEXTJOIN(";",,NoChild), TEXTJOIN(";",,Mapped)), ",", ";",,,"" ), Sorted, SORTBY(Splitted, XMATCH(CHOOSECOLS(Splitted,1),Parents)), VSTACK( HSTACK("Parent", "Child " & SEQUENCE(,COLUMNS(Splitted)-1)), Sorted ) )