Formatting and cleansing raw data

[Anmol_Mehrotra]

Hi,

I am working on a raw data set with the following example data.

Data	Email
Header A
Data A	abc@example.com
Data B	def@example.com
Header B
Data B.A	xyc@example.com
Data B.B	edf@example.com
Data B.C	edf@example.com
Header C
Data C.A	hgj@example.com

However, I want the data to be changed a bit so the headers are along the actual data. Something like below:

New Column	Data	Email
Header A	Data A	abc@example.com
Header A	Data B	def@example.com
Header B	Data B.A	xyc@example.com
Header B	Data B.B	edf@example.com
Header B	Data B.C	edf@example.com
Header C	Data C.A	hgj@example.com

I need the headers to be beside the data columns so I can have carry it after a join tool which is using Data column as the unique value to join two files.

How can I achieve this? Can anyone please help me out?

[SPetrie]

Here is one possible way to do it.

[JBLove · Accepted solution]

@Anmol_Mehrotra -

This is a good use for a Multi Row formula tool.  I used the Multi Row Formula to create the "New Field".  And then I use a filter tool to remove the rows where the New Field was blank.

Here's the Multi Row expression:  IF IsEmpty(TRIM([Data])) THEN ""
ELSEIF !IsEmpty(TRIM([Row-1:Data])) AND IsEmpty(TRIM([Row-1:Email])) THEN [Row-1:DATA] ELSE [Row-1:New Field] ENDIF

[Anmol_Mehrotra]

This worked for me. Thank you so much for the help