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…
and needed to re-arrange it into this…
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!
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?
Watch My YouTube Video
Can you think of an easier and/or more efficient solution. Remember: 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 Video, Oz demonstrates how easily ‘Get & Transform’ correctly re-arranges the data. Knowledge is power! Thanks Oz!
Learn With Me!
Last week I signed up for Power Query Academy ! Join me in this adventure! Get & Transform can do so many amazing things to fix your data and save you time! And it’s a free tool!
My name is Kevin Lehrbass. I’m a Data Analyst.
In 2017 I watched several Power Query videos. It’s an amazing tool! This year I’ve enrolled in Power Query Academy!
Check out my YouTube videos and my blog posts.
Pingback: Video 00148 Insert Rows & Cascade Numbers | My Spreadsheet Lab
Pingback: Video 00147 Conditionally Add Checkboxes using VBA | My Spreadsheet Lab
Pingback: Video 00149 Alphabetize Challenge (Oz001) | My Spreadsheet Lab
Pingback: Video 00150 Sequential Keyword Search Dog Chases Squirrel | My Spreadsheet Lab
Pingback: Video 00122 Normalizing Exported Data | My Spreadsheet Lab
Pingback: Video 00135 Cali Data Cleanup | My Spreadsheet Lab
Pingback: Having Fun with Custom Number Formats | My Spreadsheet Lab
Pingback: Re-arrange, Rotate, Transpose, Twist, Cascade the data! | My Spreadsheet Lab
Pingback: Extract Data That Could Be Anywhere! | My Spreadsheet Lab
Pingback: How Many Unique Visitors To Site Each Day? | My Spreadsheet Lab
Pingback: Brad's techniques to find Excel content | My Spreadsheet Lab
Pingback: Video: Solution_Add Sporadic Totals | My Spreadsheet Lab
Pingback: How to use vlookup formula in Excel | My Spreadsheet Lab
Pingback: My Favorite Excel Tips From Other Sites Part 2. | My Spreadsheet Lab
Pingback: Liam Bastick from sumproduct.com | My Spreadsheet Lab
Pingback: Video 00153 All Possible Pairs from List of Names | My Spreadsheet Lab
Pingback: Unstacking Data with Oz du Soleil | My Spreadsheet Lab
Pingback: 00142 Horizontal list to Vertical list (layered transpose) | CPU 4G
Pingback: Video 00159 Create unique and sorted text list | My Spreadsheet Lab
Pingback: Index of Ignorance | My Spreadsheet Lab