Clean Up Messy Data in Different Rows and Columns

[idk_code]

Hi - I currently have a very messy data set as shown below:

Column1	Column2	Column3	Column4	Column5
1010101	$500	$200	$300
$100	$600
1010102	$600	$100
$200	$300	$800
	1010103	$500	$800
$900	$100
$200

I'd like to convert the data above using the 7-digit 101010x identifier but the data is all over. Ideally, I want to begin each row with the 7-digit "ID" and read in each $ amount, excluding nulls until it hits the next 7-digit "ID". From there, the next row should begin. Below is the output I'm looking for:

ID	Cost1	Cost2	Cost3	Cost4	Cost5
1010101	$500	$200	$300	$100	$600
1010102	$600	$100	$200	$300	$800
1010103	$500	$800	$900	$100	$200

Is this possible in Alteryx? This is the first question. Sometimes in the raw dataset, there may be random text in some cells, not sure if they can be ignored. I believe the raw data set is actually too messy to be 100% solved by Alteryx. Thanks!

[grossal · Accepted solution]

Hi @idk_code,

good news for you, I think it's definitely possible with Alteryx and here is how to do it!

Output:

You can easily add another Filter to extract your additional messy data or send a sample dataset with messy rows and I'll adapt the workflow. Workflow attached.

Best

Alex

[idk_code]

Thanks grossal - this works great and was structurally foundational enough for me to finish the nuances!