SOLVED

# Help on data for preparing relationship mapping between two columns

Copper 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.

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

# Re: Help on data for preparing relationship mapping between two columns

With Excel 365/Web:

``````=LET(
HasChild, UNIQUE( FILTER(Table[Dependent Heading], Table[Dependent Heading]<>"") ),
Mapped,   DROP(
REDUCE("", HasChild,
),
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
)
)``````

# Re: Help on data for preparing relationship mapping between two columns

@Lorenzo thanks for your response.
This worked wonderfully

# Re: Help on data for preparing relationship mapping between two columns

You're welcome & Thanks for providing feedback

# Re: Help on data for preparing relationship mapping between two columns

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

# Re: Help on data for preparing relationship mapping between two columns

With Excel 365/Web:

``````=LET(
HasChild, UNIQUE( FILTER(Table[Dependent Heading], Table[Dependent Heading]<>"") ),
Mapped,   DROP(
REDUCE("", HasChild,
),
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
)
)``````