Filling in Columns Based on Rows

[smoskowitz]

Hi All --

I have a data challenge that I thought I solved -- but did not.  So what I have is a table of data (truncated here).

If you look at the second highlighted column in the second image you have McCann Parent and 3 McCann Child entities underneath. Underneath starts the next set of entities. The data is consistently structured in that you will have a parent entity and then its child entities.

What I want to do it for each parent and children is create 2 column's and put the parents Hyperion Entity Number and Hyperion Entity Name next to each of the parents and children (as seen in the second screenshot). 

What I tried to do: Was create a mapping based on the Active dormant column as it is only present for Parents, but this breaks when a child is in a different country then the parent. I am guessing there is something that can be done using Multi-Row formula, but I have not been able to figure it out.

Thanks for your help.

Seth

With new fields

[danrh · Accepted solution]

Try something like this:

I'm using the Multi-Row Formula to give each Parent/Child grouping a unique ID.  Then I split off the Parents and re-join them based on that ID to get the Parent name/number on every record.

Hope it helps!

[smoskowitz]

Thank you! That worked!