Challenge #20: List Parsing

[michalsicak]

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

Spoiler

[jamielaird]

Solution attached. Merry Christmas everyone :-)

Spoiler

[Suzanne]

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

[Natasha]

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

[MaxGiegerich]

Ooft. Death by parsing.

[JoshKushner]

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

[JosephSerpis]

Challenge Completed

[mceleavey]

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:



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:

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.

Regex makes me happy.

[asabau]

Quite difficult. Peaked into other solutions posted

[blairmbailey]

Solution attached - thanks!