Forum Discussion

yogi_new's avatar
yogi_new
Copper Contributor
Jan 08, 2024
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...
excel
Excel for web
Formulas and Functions
Macros and VBA
office 365
  • Lorenzo's avatar
    Lorenzo
    Jan 08, 2024

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

Resources