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.”
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.
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 n 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
The 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!
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 x 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.
10 Responses to “Reshaping your data easily – Case study [Pivot tables FTW]”
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.
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)
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.
In case Col A and Col B were text values then you will have to first add Index column and then perform all above steps considering Index column as Column A and delete Index column before performing unpivot.
Checkout Power Query training course where similar case study explains above method in detail -
Where and How should I ask question, please let me know
What about this formula? It's a bit of a mouthful, but.
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?
1. Import both the sheets in power query.
2. Add Index column for both sheets
3. Join both sheets using Index columns
4. Follow the steps mentioned in Karimmo comments.
For more information on Power Query checkout this course
Power query is my go to data transformation tool if I want to build something robust and reusable.