fbpx
Search
Close this search box.

Reshaping your data easily – Case study [Pivot tables FTW]

Share

Facebook
Twitter
LinkedIn

Late. Jayaram, my uncle is also a teacher. When I was a kid, I used to spend a lot of time with him, learning all sorts of things. He taught me chess, maths and so many life lessons. I remember one such lesson very vividly.  One day, he asked me to do something. I did it in a very long way. After seeing me struggle for several minutes, he chipped in and showed me how to do it easily. He then said, “when someone asks you where your nose is, you don’t twist arm around your head. You just point to your nose directly.”

show-me-your-nose

The idea is that when you have a direct, simple way to do something, you should use it.

Nose and pivot tables… how are they connected?

We are coming to the point. Recently, learneagerly, one of our forum users asked a question about how to transform (reshape) a set of data in Excel.

Marc L, one of our Excel ninjas, posted an awesome VBA script to do that.

Later in the day, I chipped in and shared a formula approach to transform the data.

I suggest checking out both approaches for learning more about VBA & INDEX formula respectively.

After posting my answer, I got thinking… May be there is a more direct way to reshape the data.

Why, yes, there is. You can use Pivot Tables.

Let’s take a look at the data & problem first

Here is a snapshot of raw data and expected output.

data-and-expected-output-reshaping-with-pivots

As you can see, we have two columns of data and we need to extract n (here it is 6) items from first column, then from second column and lay them out in output. We repeat this until we run out of the data.

Reshape this thing with a Pivot

raw-data-reshape-with-pivots-v1The first step is to add two extra columns to your raw data. Let’s call them Running & Repeat.

  • Running: with numbers 1 thru 6 and repeat the pattern (just auto fill or copy paste)
  • Repeat: with 6 cells of 1, 6 cells of 2… and repeat this pattern (auto-fill)

But what if I want a different n

Even better. use formulas. Let’s say your data starts from H6:I6

  • Running:  =MOD(ROWS($I$6:I6)-1,n)+1
  • Repeat:  =IF(J6=1,SUM(K5,1),K5)

Related: Using ROWS() to generate running numbers in Excel.

Now that we have these extra columns, select all the data (2 columns of data + 2 extra columns we just added) and insert a pivot table.

Set up the report by,

  • Adding Repeat & Running to row labels area (in that order)
  • Add Col A & Col B to values area.
  • Move the ? values to row labels area (by dragging it)
  • Position ? values between Repeat & Running row labels.
  • Your pivot report’s last column will have the transformed data.
  • Viola, nose pointed!

pivot-table-ftw

 

Download Example Workbook

Here is the example workbook. Examine the pivot table & formulas in Running & Repeat columns to learn more.

Get your Excel muscles in to shape

Are you struggling to find your nose or worse still, twisting your arm on the way? If so, check out our Excel school program. We have awesome online lessons, beautiful explanations, powerful techniques and easy to understand downloads. It won’t be long before you are smelling roses.

Check out our Excel School online class & join today.

How do you reshape your data?

Pivot Tables and Power Query are my go to tools for almost all kinds of reshaping problems. Often, I indulge in INDEX formulas or a bit of VBA. For example, just a few days ago, I had to split first 100,000 digits of Pi ? in chunks of 3 digits, 3 digits and 14 digits in a pattern. As the data is too long, loading it Excel cell was impractical. Loading it in to multiple lines with each having digits was impractical (as I may need to split them in another pattern). So I used a simple VBA script to zap the data and get what I need.

In case you are curious: I made a chart to celebrate the Pi day (14th of March) with our community on Twitter.

But when I am not splitting irrational stuff, I usually rely on Pivot tables or PQ.

What about you? How do you reshape your data? Please share your approaches and tips in the comments section.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

10 Responses to “Reshaping your data easily – Case study [Pivot tables FTW]”

  1. Karimmo says:

    It's pretty easy to make it in PowerQuery. Just 2 steps right from the interface (no formula writing required) . Upload your original table to Power Query.
    1. Select Column 'Col A' and Click -> Add Column -> Standard -> Modulo + enter '6' - and you have your Running column
    2. Select Column 'Col A' and Click -> Add Column -> Standard -> Divide Integer + - enter '6' - and you have your Repeat column.
    And that's it.

    • Karimmo says:

      Small adjustment required for proper grouping:
      just add '-1' after column name in both formulas created by interface:

      AddRunningCol = Table.AddColumn(Source, "Running", each Number.Mod([Col A]-1, 6), type number),

      AddRepeatCol = Table.AddColumn(AddRunningCol, "Repeat", each Number.IntegerDivide([Col A]-1, 6), Int64.Type)

      • Karimmo says:

        With 3 more clicks you can reshape completely so that Pivot Table is no longer needed:
        1. Select [Col A] and [Col B] columns - and click Transform -> Unpivot
        2. Sort Asc 3 columns in order: [Repeat], [Attribute] and [Running]
        3. Remove all columns except of [Values] - and load to spreadsheet.

    • Syed Quadri says:

      Where and How should I ask question, please let me know
      Thanks

  2. Rossa says:

    What about this formula? It's a bit of a mouthful, but.

    =IF(ISODD(ROUNDUP(ROWS($D$2:D2)/6,0)),INDEX($A$2:$A$19,ROWS($D$2:D2)-6*(ROUNDUP(ROWS($D$2:D2)/6,0)-ROUNDUP(ROWS($D$2:D2)/12,0))),INDEX($B$2:$B$19,ROWS($D$2:D2)-6*(ROUNDUP(ROWS($D$2:D2)/6,0)-ROUNDUP(ROWS($D$2:D2)/12,0))))

  3. Richard says:

    Any idea how you would do this if column A was in one worksheet and column B in another and you wanted the result in a different worksheet?

  4. John says:

    Power query is my go to data transformation tool if I want to build something robust and reusable.

Leave a Reply