Parsing Unstructured Data from rows and columns into columns

[Nageen]

Hello,

I am trying to parse the attached unstructured data set into structured columns. I am having trouble since I need to parse both rows and columns. The example output data tab is how I'm hoping to structure the data.

This data was originally in a text file which I copy and pasted into an Excel file. If a solution could be created using only the text file, that would be appreciated too.

Thank you in advance for your time and any guidance.

[estherb47 · Accepted solution]

I’ll take a look when I’m on my computer, but you could also adjust the
Tile tool to reflect the number of rows. It’s currently set up to slit into
2 groups.

And yes, Two multirow formula tools will work, one to set the groups, and
one to set the order. Both are new integer fields

Eg. groupID. If Contains([F1],“username”)

then [Row-1:groupID] + 1

Else [Row-1:groupID]

endif

The for the second, let’s call it header
If Contains([F1],“username”)

then 1

Else [Row-1:Header]+1

endif

Then a tweak to the Cross Tab and Dynamic rename tools to use the new fields 
--
Thanks @jasperlch. That was actually my original approach. Tried to be clever with the Tile tool, not thinking that this flow should be more dynamic.

[Nageen]

THANK YOU @estherb47 and @jasperlch! That solution worked perfectly!