Transferring the contents of a column

[gkcekucuk]

Hi,

I am working with a dataset that has three columns for the same information. For example, there are three columns for tenant information; tenant1, tenant2, tenant3. If there is only one tenant, it should always be in tenant1 and the rest should be empty but that is not always the case. So if tenant 1 is empty and tenant 2 is not empty, I want to write what is in tenant 2 to tenant 1 and remove what is in tenant 2 column. And also do the same between tenant 2& 3 columns. Also if only Tenant 3 is filled I would like to write that info to tenant1. 

For example, this is the table I have now.

Tenant1	Tenant2	Tenant3
Company A
	Company B	Company C
		Company D

And I would like the table to look like this after the ETL process: 

Tenant1	Tenant2	Tenant3
Company A
Company B	Company C
Company D

Is there a way to do this? I can write the columns with the formula tool but I am not sure how to remove the original columns after I put the information in the first column. Thanks! 

[cjaneczko]

Does this work? 

Insert a Record ID, then cleanse all of the data to remove blanks/nulls. Transpose the data by grouping on the RecorID. All other columns are Data columns. Filter out any of the blank/empty values. Generate a new row count with a multirow formula then transform and cross tab based on that new field.