Group similar columns by name to clean data

[Anmol_Mehrotra]

Hi

I am looking to clean data before it could be added to the final output. Currently the raw data is following:

Header	Value 1	Value 2	Value 3	Value 4
ABC	123		23XY
ABC		2543		3u5
DEF			9786
DEF	342t	665h
DEF				<32
GHI

However, I want to clean the data and have it appear something like the following so it can be further joined properly:

Header	Value 1	Value 2	Value 3	Value 4
ABC	123	2543	23XY	3u5
DEF	342t	665h	9786	<32
GHI

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

[Qiu · Accepted solution]

@Anmol_Mehrotra 
We use the combination of Transpose and Cross Tab as below.

The additonal part is for the Null row.

[adavis1]

This was a bit tougher than I thought it would be.  I had to get rid of the null values, group it by header and concatenate each value in the summarize tool.  This leaves you with some commas (added by alteryx in the concatenation).  It is worth mentioning that this solution will not work well if you have more than one value per header.  For example, if ABC value 1 was 456 on one line and 123 on the other, you would get 456,123.  Then a formula tool is need to get rid of the commas and place the result in a new column as follows: Replace([Concat_Value 2], ",", "").  After that, I just got rid of the concat columns and I was done.  I feel like there might be a cleaner way to do this, but this works.

[flying008]

Hi, @Anmol_Mehrotra 

FYI.