Need help with the ugliest set of data I have ever seen

[RCern]

So, some software engineer decided it was a good idea to create a report that dumps several data elements for 100's of customers into a single column.  I have tried my best using the text to columns tool to parse the data, but I keep running into brick walls.  I am hoping some of you pros can help me solve this problem.  I don't have a workflow to share because I get too frustrated and keep deleting them.  What I do have is an "Original" file that is a mocked-up version of what the data looks like for 3 sets of customers and an "End State" file of what I would like Alteryx to transform the data into.  Is any brave soul willing to take a crack at this?

[RCern]

@griffinwelsh do I move the multi row formula after each regex tool or leave it as is?  Also, what is the "F1_Matched" regex tool doing?

[griffinwelsh]

@RCern you can leave it as it is in my last post. F1_Matched is checking for rows that only have a dollar amount and returning true or false. This works by checking for the following format in the string:

[$ 0-9,]+ one or more digits, spaces, or dollar signs at the start of the string

[.] one period

\d{2} two digits

[*]* 0 or more asterisks

[RCern]

@griffinwelsh thank you!  Another quick question.  In the MultiRow formula for Acct Admin, how would I adjust the formula to keep what it currently says but also add a condition to  leave it null if the name appears in any of the rows above it?   I'm trying to solve for the Luke Skywalker Admin having a non conforming layout and his setup not meeting the origional condition like the others.

[griffinwelsh · Accepted solution]

@RCern Take a look at this. You can't do that within the same tool but this has the desired effect. This matches your desired output except it has the row for John Jones XYZ Company. Based on the file I don't know what indicates that this should be skipped.

[RCern]

@griffinwelsh   I think John Jones XYZ company is being skipped because his total of $1400 doesn't include ".00" the cents that the others do.  I think the RegEx expression for the Match is specifically looking for cents.  How do I adjust it to look for cents and no cents?