My friend Mo works in the areas of branding and marketing. He text messaged me over the weekend, but I didn’t see it until about 5 hours after he’d sent the message. He was asking if I had a second to go over some Excel stuff with him. By the time we did talk on the phone, he’d made good progress but was also sounding a bit exasperated. He said, “I figured out how to get the first and last names separated into 2 columns. But, there’s this other thing that I thought I figured out, but it didn’t work all the way.”

Mo described the challenge this way (I’ve modified the example to protect the real data, but the concept remains intact):

There are 3 possible means of contacting a person; and an order of priority has been established. In the example below:

• 1st Priority: Email
• 2nd: Mobile Phone
• 3rd: Alternate Phone

And there’s a 4th option: “no contact info” for people who don’t have a contact method listed.

The image below has yellow highlights for the data that needs to be brought over to CONTACT METHOD, and a red x shows where there is “no contact info.”

The Solution is in the final section of this blogpost

Mo said that he went onto forums and blogs and saw explanations that he didn’t understand enough to modify the solutions to fit his challenge. So he completed this piece of the project manually. And then there was the big question:

“Oz, is there any place you can suggest that would
break this down into simpler explanations?”

Clearly, he wants to learn this stuff and be able to apply solutions quickly. And what was my response?

“Brother, you don’t know it, but you’re into some heavy shit.”

Mo was on the back of a bull called Gnarly Data, and was doing a pretty good job.

WHY IS THIS SO HEAVY?

Mo was deep into some real world data-cleansing and data-shaping. He’d already parsed the first and last names. He still had duplicate removal to contend with later; and in the middle of it all, he needed to populate the CONTACT METHOD field. This was beyond the stand-alone tips that many of us create in our videos and blogs. He was dealing with layers and strategy.

Also. In this sport of data-cleansing, there are so many one-off tasks, things you’ll see one time and never again in life. In fact, over more than 15 years of working with Excel, I’ve never had to solve the challenge Mo set before me. Therefore, solutions have to be conceptual and creative. To quickly develop solutions, it takes a wide variety of constant experience. Then, you get to see more features of Excel and the many ways to weave them together for these unusual tasks. Basically:

You’ve gotta ride a lot of bulls!

We have to do things that don’t have a name. ‘Dependent dropdown list’ has a name. There is no name for: “populate a cell based on prioritized content in other non-contiguous cells in the same row.”

THAT’S A BULL RIDE!

By going into forums, Mo may have seen the solutions that people worked out using array formulas on contiguous cells. But those solutions wouldn’t be easy for a new analyst to retrofit for their real-world non-contiguous cells.

That’s the long answer to: “is there a resource that breaks this stuff down into simpler explanations?”

The short answer: For common tasks, yes. For these exotic tasks however, it takes some effort to be able to take your own specific task, find a solution to a problem that’s close enough, and customize your own solution.

THE GOOD NEWS!

I heard an Excel MVP describe Excel power-users as such: “we’re a bunch of hacks who started working with Excel out of necessity, and just kept learning until we knew more than the people around us.” I will add that most of us had some other role when data issues showed up. I was in customer service. Mo is in marketing.

So, Mo is where most of us started: thrown into unique situations and having to figure it out, especially in the world of data-cleansing and data-shaping.

That’s the good news for not only Mo, but anyone who is struggling to get cooperation with Excel.  Mo, is doing the right things by going into forums and trying to write his own formulas. Also, one beautiful thing about Excel is that most folks on this Earth are likely to have at least 1 friend whom they can call on a Sunday night and get help with Excel.

Mo, welcome to the dirty sport of Data Management! You’re one of us. Here’s your hat:

THE SOLUTION (HOW TO STAY ON THIS BULL)

=IF(D2<>””,D2,IF(F2<>””,F2,IF(H2<>””,H2,”no contact info”)))

In cell J2 I used the formula above. Translation:

If D2 does not equal nothing, then retrieve the contents of cell D2
otherwise
If F2 does not equal nothing, then retrieve the contents of F2
otherwise
If H2 does not equal nothing, then retrieve the contents of H2
otherwise
input “no contact info”

Meaning: If there’s something in a cell, grab the contents and stop. However, if there’s nothing in a cell, move to the next cell, and if all 3 cells are empty, say “no contact info.”

That’s the simplest, straightforward solution. Nested IF statements.