Challenge #53: Parsing Report Formatted Data

[ckoo22]

Solution Attached!

[Krzysiek]

MySolution

Spoiler

[AGilbert]

I believe I found a 'dynamic' way using the core tools. Namely split to rows and cross-tab/transpose pivots. At least under the assumption the values will always be delimited with a pipe. 

I tried a long regex expression for 'fun', but the third record's null client id got me. The expression I tried was (\#|\d{4}|"") but it fails to match. My solution was to make the entire clause optional as (\#|\d{4})*. Pretty much the opposite of dynamic but was good practice.

Spoiler

[AllexJia]

I just set a large number in the Text to Columns tool```I'm not sure wether this works or not.

[AllexJia]

my solution  as attachment.

Spoiler

[Raj]

Solved !

[Jon-Knepper]

Here is my solution!

Spoiler

[Bobbyt23]

Really enjoyed that challenge. The power of the macro!

[mshishido]

here

[danielhoge]

Spoiler

allows up to 100 columns, rejects any that have the generic "Field_" column name after the dynamic rename tool is used, but still allows 'unknown' columns in case new column values are used.