Who knows what could happen in the future but for now humans using software cleanup and analyze data (not nerdy robots!). This post reviews examples of awkwardly twisted data that require transposing, rotating, repeating, etc before it can be properly analyzed.

Is Your Data Twisted In Knots, Backwards Or Upside Down?

Let’s look at the most common methods to restructure data and then several examples.

Data Normalization6

 

Methods To Restructure Data

  • Manual – if it’s a small data-set, a clear pattern, and a 1 time task then the good ol’ copy/paste might be the best option.
  • Formulas – if you haven’t learned VBA or Get & Transform you can often use formulas to re-arrange your data.
  • VBA – great way to re-arrange your data especially if it’s a re-occurring requirement. Spend the time upfront to save many hours later on!
  • Get & Transform – you don’t have to be a programmer to learn! If formulas are too slow and you don’t want to learn VBA then try Get & Transform!
  • Pivot Table – what? The old ALT D P trick can properly un-pivot some patterns. It doesn’t work in all cases but if you’re lucky it can make your day!

 

Let’s Look At Some Formula Solution Examples

‘VIDEO 00122 NORMALIZING EXPORTED DATA’ All columns of data are stacked into a single column. It’s almost impossible to analyze.

00122 turn this into this

What should we do? Define the pattern & re-arrange the data. MY SOLUTION (YouTube)

 

 

‘VIDEO 00135 CALI DATA CLEANUP’ As you might guess, the data didn’t belong to my dog Cali. This fictitious sample follows the same pattern as the actual data (which was thousands of rows). I combined several steps to create this pivot-able data set.

00135 turn this into this

Now we can easily pivot the data or analyze with formulas. MY SOLUTION (YouTube)

 

 

‘VIDEO 00142 HORIZONTAL LIST TO VERTICAL LIST (LAYERED TRANSPOSE)’ Along with re-arranging data we need to repeat certain values.

00142 turn this into this

Easier than the previous example but it still requires a few steps. I can see how VBA could solve this but I used formulas of course! MY SOLUTION (YouTube)

 

 

‘VIDEO 00148 INSERT ROWS, CASCADE NUMBERS’ We’ve been asked to repeat each row 3 times but only repeat 1 number each time.

00148 turn this into this

Programmers would start writing VBA code. Me? I used a Bob Umlas trick along with a few other steps to quickly solve this. MY SOLUTION (YouTube)

 

 

‘VIDEO 00117 TRANSPOSE STACKED TABLES’ Once again, all the data in stacked in 1 column but the pattern was more consistent & easier to solve.

00117 turn this into this

Ken solved this with Excel’s Get & Transform feature. Oz du Soleil is also a big Get & Transform fan!

I solved it with a few easy formulas as the pattern is consistent. MY SOLUTION (YouTube)

Here is another similar case of stacked data that we need to fix! MY SOLUTION (YouTube)

 

 

Once Data Is Re-arranged Analyze It With Pivots And Formulas!

Nerdy Robot rearranges data_ 7-30-2016 11-22-14 AM

 

Do We Always Have to Restructure Data Sets?

It usually makes sense to restructure our data so that it’s easier to analyze it with formulas and pivots. However, there are exceptions when we can use some complex formulas to get the answers we need without any restructuring.

 

What Awkward Data Sets Have You Re-structured?

Add a comment below describing how you have re-structured a data set or how you used formulas to analyze your data without restructuring.

 

About Me

KevinInMunich_My name is Kevin Lehrbass. This is my personal blog about Microsoft Excel. I live in Markham, Ontario, Canada.  I’ve been studying, supporting, building, troubleshooting, teaching and dreaming in Excel since 2001. I’m a Data Analyst at McKinsey & Company.

Thousands of hours can be saved and costly errors can be avoided or corrected if you study this powerful software. Check out my videos and blog posts.

Away from Excel I enjoy playing with my dog Cali, learning Spanish, playing Chess and drawing nerds.

, learning Spanish, playing Chess and drawing nerds.Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Post a comment