We don’t always find out how data got twisted into awkward forms. Our job is to fix it by normalizing, unwinding, transposing, etc. Once the data is in the correct format we can then get to the real job of extracting meaning from it.

 

In this case, a member of Mr Excel’s forum was asking for a VBA solution to a “layered transpose” data issue. WHAT IS THAT? I’ll explain…

He had data in this layout…

000142 Original Format

and needed to re-arrange it into this…

000142 Desired Format

 

VBA Solution

For VBA solutions here’s the link to Mr Excel’s forum. It’s a great forum with so many Excel experts. If you haven’t already registered then you should!

 

Formula Solution

I solved this challenge with formulas. For repetitive long term cases (especially with large data sets) VBA is usually the better way to go. Sometimes a formula based solution has benefits. For example, it’s often easier to explain step by step formula solutions to non technical people.

Download my Excel file here or from my OneDrive.

 

Does My Dog Prefer VBA or Power Query?

Cali_20160116_00142
For some reason Cali kept barking when I tried to record the video today.
She didn’t bark or want to play fetch when I was watching TV.
Is she trying to tell me something?
Could there be an easier formula solution? Maybe Cali prefers a VBA or Power Query solution.
I bet Mike Girvin & Ken Puls could solve this using Power Query.

 

 

 

 

 

 

Watch My YouTube Video

My YouTube Video

See if you can think of an easier and/or more efficient solution. Remember that the solution should be ‘readable’ in logical steps.

Do You Have A Better Formula Solution?

If so, leave a comment below.

 

Better Solution Update!

Even though I was asking for alternative formula solutions Oz du Soleil reminded me how incredible Excel’s Power Query (aka ‘Get & Transform’) feature is.

In his YouTube Video, Oz demonstrates how easily ‘Get & Transform’ can correctly re-arrange the data. Knowledge is power! Thanks Oz!

 

Learn With Me!

Last week I signed up for Power Query Academy ! Join me in this adventureGet & Transform can do so many amazing things to fix your data and save you time! And it’s a free tool!

 

About Me

My name is Kevin Lehrbass. I live in Markham, Ontario, Canada.

In 2017 I watched several Power Query videos. It’s an amazing tool! This year I’ve enrolled in Power Query Academy!

I’m a Data Analyst at McKinsey & Company.

Check out my YouTube videos and my blog posts.

22 Comments

Post a comment