How to separate multiple entries from a cell into rows?

Question

This is my dataset currently

EntityOwner EmailsBuyer EmailsABC Incjohn@test.com
Pam@ abc.comJim@yahoo.com
James@abc.com
Kelly@cbs.comABC IncBob@msn.comtest@test.com
Greg@test.com
Sam@cnn.comCBC IncSal@cbc.com
Tom@jerry.comPaul@test.com

I want to get it into this format.

EntityUserTypeABC Incjohn@test.comOwnerABC IncPam@ abc.comOwnerABC IncJim@yahoo.comBuyerABC IncJames@abc.comBuyerABC IncKelly@cbs.comBuyerABC IncBob@msn.comOwnerABC Inctest@test.comBuyerABC IncGreg@test.comBuyerABC IncSam@cnn.comBuyerCBC IncSal@cbc.comOwnerCBC IncTom@jerry.comOwnerCBC IncPaul@test.comBuyer

I essentially want a separate row for each email, and then add a column to say what type of owner it is (owner vs buyer).  Can anyone help?

Kenda · Answer

Hi @richleeb2

They key here is a Transpose tool.

First, though, I would use a multi-row formula tool to "fill down" your Entity field:

Then, use the Transpose to essentially move the field names to rows. Last, I would use a Filter tool to get rid of the empty values.

Hope this helps!

New Workflow5.yxmd

JosephSerpis · Answer

Hi @richleeb2 you can do this with a transpose tool and a text to columns tool.

Spilt_to_Rows_09122021.yxmd