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?

[griffinwelsh]

@RCern I hate to give you a half baked solution, but this is pretty close and I am out of time to work on the solution. Hopefully you can take it from here. 

[RCern]

@griffinwelsh Thank you so much!  This is incredible.  I've only gotten the Core Certificate so some of these tools are unfamiliar to me so it will take me a while to digest what is going on lol.

[RCern]

@griffinwelsh If at all possible, can I aske a favor.  I just found out that I can strip out a majority of the data, so I am hoping this is easier.  I need the EndResult to just feature the Account Admin, the Company Name and the Total Amount for the Company.  I modified both the Original File by highlighting the fields we need and putting the fields you can ignore in red.  I also adjusted the Final Result file.  Would you be able to help me take another stab at a simplified workflow?

[griffinwelsh]

@RCern How is this? I have a few extra rows because I don't understand the pattern for picking group rows (some are highlighted some are not) but otherwise It think this is everything you needed.

[RCern]

@griffinwelsh Thank you!  I know, it's iffy.  Basically an Account Admin can have multiple groups under them.  We are trying to pull the total amount for each group.   How much would this workflow change if the source file was formatted the same exact way but it was a .TXT file?

[griffinwelsh]

@RCern did my solution work for you? Using a .txt should not change the results assuming it has appropriate delimiters. The only way to to know for sure is to try it though.

[RCern]

@griffinwelsh I'm traveling today but will review it tonight and let you know.  I greatly, greatly appreciate your time!

[RCern]

@griffinwelsh First of all, you are amazing.  I only have the core certificate and this makes me want to take the advanced.  

How could I edit the RegEx so that it populates the Acct Admin and Group column only when the row below it is blank/null?  I think this will help streamline when to pull those. 

For the Total Amount, it should pull the $ when:

• the row above it is '--------------' and
• the second row down from the $ is populated with '1             90 Days...' or begins with 'Group....' (This is because some Acct Admins have multiple groups that should have a total for each group.

For Example, Acct Admin Peter Pan has 2 groups (Krusty Krab $45,072.39 and Spongeshack $10,504.87)

I adjusted the Original file as such.  Please see attached.

[griffinwelsh]

@RCernI don't think this can be done in regex. Best to do this with a multi row formula after each regex tool. Formula format:

if length([Row+1:F1]) < 1 then [Acct Admin] else null() endif