SOLVED

Help on data for preparing relationship mapping between two columns

Copper Contributor

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.

4 Replies
best response confirmed by yogi_new (Copper Contributor)
Solution

Hi @yogi_new 

 

Sample.png

 

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 thanks for your response. 
This worked wonderfully:hearteyes:

You're welcome & Thanks for providing feedback

@yogi_new 

 

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

1 best response

Accepted Solutions
best response confirmed by yogi_new (Copper Contributor)
Solution

Hi @yogi_new 

 

Sample.png

 

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

View solution in original post