Forum Discussion

yogi_new's avatar
yogi_new
Copper Contributor
Jan 08, 2024

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:

HeadingCategoryDependent Heading
Table 1PARENT 
Table 2PARENT 
Table 3PARENT 
Table 4CHILDTable 3
Table 5PARENT 
Table 6CHILDTable 5
Table 7CHILDTable 5
Table 8PARENT 
Table 9CHILDTable 8
Table 10CHILDTable 8
Table 11CHILDTable 8
Table 12PARENT 
Table 13PARENT 
Table 14PARENT 
Table 15CHILDTable 14
Table 16CHILDTable 14

 

I need to have the data mapping done in below listed format.

Expected Output:

ParentChild 1Child 2Child 3
Table 1   
Table 2   
Table 3Table 4  
Table 5Table 6Table 7 
Table 8Table 9Table 10Table 11
Table 12   
Table 13   
Table 14Table 15Table 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
      )
    )
  • Lorenzo's avatar
    Lorenzo
    Silver 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
      )
    )

Resources