Struggling Achieving Specific Output - Need to find a solution fast!

[32519]

Hi All, 

I am struggling to achieve a specific output based on a certain set of data and rules.

Current data is as follows:

Owned Entity	Owner	Ownership Percentage	Owned Type	Owner Type	Related Family
M Corporation	A	75	Corporation	Individual	AW
M Corporation	AW	25	Corporation	Individual	A
N/A	AWF	0	N/A	Individual	AW
X Corporation	AW	20	Corporation	Individual	A; AWF
O Corporation	A	10	Corporation	Individual	AW

Output I am looking to get:

Owned Entity	Owner	Ownership Percentage	Owned Type	Owner Type	Related Family	Owned Entity1	Attributed Family Ownership	Owned Entity2	Attributed Family Ownership 2
M Corporation	A	75	Corporation	Individual	AW	M Corporation	25	X Corporation	20
M Corporation	AW	25	Corporation	Individual	A	M Corporation	75	O Corporation	10
N/A	AWF	0	N/A	Individual	AW	M Corporation	25	X Corporation	20
X Corporation	AW	20	Corporation	Individual	A; AWF
O Corporation	A	10	Corporation	Individual	AW

Basically, the rule is you get attributed the ownership % in an entity if you are related to that person.  I tried using the find and replace tool in alteryx but the output gets messed up with a person owns more than two entities. 

As long as I get the information in red text as an output, I am not picky about how it represented within Alteryx.

Can someone please help with finding a solution? I've been trying to figure out the best approach.  Many thanks in advance!

[jasperlch · Accepted solution]

Hi @32519 ,

Not 100% sure if I get your question correctly. But I believe Transpose and Cross Tab might help.

Attached is the sample workflow.

[32519]

Thanks @jasperlch.

This is pretty much what I needed.  I just need to make one tweak.  

Thanks for the quick response!!!