Pivoting and Parsing data

Question

I have a ton of data that is set up similarly to the Sample Input attached.  I am trying to take the Sample Input and make it look like the Sample Output.  I have tried pivoting, transposing, RegEx, and a combination of all.  I have been searching the discussion boards for days and finally decided to post.  This is my first post ever and will not be my last.  The Community here is amazing and I have learned so much from everyone the short time I have been using Alteryx.  Any help would be greatly appreciated.

Sample Input.xlsx

Sample Output.xlsx

apathetichell · Accepted Answer

this is yucky data. You can start by using a multi-row formula to create an index. You use:

if [Row-1:F1]=null() then [Row-1:index]+1 else [Row-1:index] endif (with values that don't exist set to 0) and create a new index.

you use tile tool (unique value/index) - to get your number of fields.

you filter for nulls.

you crosstab (f1 becomes your values, tile secquence number is your column header and tile number is your primary key).

now comes the annoying part. addresses are particularly annoying to divide up. I'd recommend using something like:

regex_replace([2],"^(.*),(.*),","$1") for the street column

I'd recommend something like left(replace([2],[street column]+", ",""),2) for the state

you can then try something simlair to extract zip. you can also try regex_replace([street column],"^(.*, \w{2} )(.*)","$2") to capture your zip.

TimN · Accepted Answer

I agree that Regex is the way to go for parsing.  My regex is not good so I did it without.  There has to be a better way but here's my attempt.