Forum Discussion
Solved
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 tabl...
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 ) )
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
)
)
