Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

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 #20: List Parsing

michalsicak
7 - Meteor

phew, almost gave up a couple of times and definitely solved this in a very complicated and error-prone manner.

 

Spoiler
challenge20.png
jamielaird
14 - Magnetar

Solution attached. Merry Christmas everyone :-)

 

Spoiler
Screen Shot 2017-12-25 at 12.30.42.png
Suzanne
7 - Meteor

It's a bit messy but good learning so left some of the trials and tribulations in the final solution -:)

Natasha
9 - Comet

It took me quite a lot of time to do this one. Was trying to match through the company name position between blanks/nulls, but that was all very inconsistent. Finally gave up and did it with regex as everyone else.

Spoiler
Screen Shot 2017-12-26 at 22.15.11.png
MaxGiegerich
8 - Asteroid

Ooft. Death by parsing.

JoshKushner
12 - Quasar

By far took me the longest of any, but after understanding there was the address/phone combo it worked out.

JosephSerpis
17 - Castor
17 - Castor

Challenge Completed

mceleavey
17 - Castor
17 - Castor

A beautiful example of why Regex is the best thing since Otto Frederick Rohwedder embraced his powerful laziness and made an paralleled contribution to society.

I suspect my solution differs slightly to most in form if not logic:

 

Spoiler
I first replaced the solitary "." fields, then individually isolated the Fax, Phone, Website and Notes fields using Regex:
Regex.PNG

Fax.PNG
I then transposed the data to create a single value column with these fields and used the Find and Replace tool to remove them from Field_1. This left Field_1 containing only Company Name and Address:
Transpose.PNG
I then used Regex to create the Address field and a simple replace formula to isolate the company name.
It was then a simple method of creating a record id and summarizing using the record id and company name as the group and the longest values of each column as the values.
Workflow.PNG


Regex makes me happy.

 



Bulien

asabau
8 - Asteroid

Quite difficult. Peaked into other solutions posted

blairmbailey
8 - Asteroid

Solution attached - thanks!