Challenge #53: Parsing Report Formatted Data

[Tokimatsu]

Solved.

Spoiler

 

[JasonHu]

Spoiler

Here is my submission for challenge 53.

[OwenBData]

Really enjoyed this one! Good practice and probably will be quite useful in the future!

Made it a macro - only problem is I wasn't sure how to 'hide' the 'number of columns' box. Is there a solution to update a value in the text to columns 'number of columns' without it showing on the macro interface? 

Spoiler

[TraceyD]

Here's my solution

[OnionKnight]

Fun!!

[clmc9601]

My solution

[Jude451]

Done

[ReconBen]

Spoiler

 

[Pang_Hee_Choy]

my solution

[apathetichell]

It's fascinating to see how wildly different many of the approaches are! I kept the data in the source file as formatted there (ie - ID codes aren't separated by commas and didn't turn blanks into N/A). I used a regex to turn the non-data rows empty. then formatted out, text to columns (rows), transpose, a multi-row to assign future row numbers, cross-tab followed by a text to columns to take out the concatenated data (note - I used excess columns here to abide by the instructions of unknown future columns) and proceeded from there (dynamic rename and the usual)....