Weekly Challenges

Solve the challenge, share your solution and summit the ranks of our Community!

Also available in | Français | Português | Español | 日本語
IDEAS WANTED

Want to get involved? We're always looking for ideas and content for Weekly Challenges.

SUBMIT YOUR IDEA

Challenge #53: Parsing Report Formatted Data

ckoo22
8 - Asteroid

Solution Attached!

Krzysiek
8 - Asteroid

MySolution

 

Spoiler
Capture.PNG
AGilbert
11 - Bolide

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
Screenshot 2024-03-20 130040.png
AllexJia
8 - Asteroid

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

AllexJia
8 - Asteroid

my solution  as attachment.

Spoiler
 

11.png

 

Raj
15 - Aurora

Solved !

Jon-Knepper
8 - Asteroid

Here is my solution!

 

Spoiler
Untitled.png
Bobbyt23
9 - Comet

Really enjoyed that challenge. The power of the macro!

mshishido
8 - Asteroid

here

danielhoge
8 - Asteroid
Spoiler
Screenshot 2024-07-03 122746.png

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.