• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Transforming data for Pivot analysis


New Member

can you help me with some data manipulation:


I have CSV file that is structured as file posted in Visualization Challenge #2.

What I want to do:

To more easily analyse data Pivot is used, but data needs to be changed in table where daily sales values are not positioned right to left but as one after another. I do that so I can add MONTH and QUARTER and YEAR columns.

This is just example - real problem is that I get monthly similar CSV data that is different every month (different no of rows).

Thanks for your help,



Excel Ninja
Staff member

I'm not sure I totally understand you but are you aware that as long as you have a Date field, Pivot Tables can group answers to Months, Quarters and Years as an option, you don't need separate fields


New Member

I am aware of this great functionality.

Let me explain.

I have data in this format (example):

Person, Product, Region, 1.1.2010, 2.1.2010, 4.1.2010, 7.1.2010 (HEADER)

Luka, hammer, Reg1, 234,443,2322,333 (LINE ITEM, Where numbers are Sales on the DATE)

Hui, hammer, Reg2, 444,3323,556,8898

Mike, nail, Reg1, 32434,343,3556,9921

Hui, nail, Reg2, 343,567,990,543

I would like to put this format in Pivot structure:

Person, Product, Region, Sales, Date

Luka, hammer, Reg1, 234, 1.1.2010

Luka, hammer, Reg1, 443, 2.1.2010


Hope this clarify my Q.

Thanks for help,



Excel Ninja
Staff member

In J1:N1 put
Person     Product     Region    Sales    Date
as titles


J2: =OFFSET($A$1,+INT((2+ROW())/4),0,1,1)

K2: =OFFSET($A$1,+INT((2+ROW())/4),1,1,1)

L2: =OFFSET($A$1,+INT((2+ROW())/4),2,1,1)

M2: =OFFSET($A$1,+INT((2+ROW())/4),3+MOD(2+ROW(),4),1,1)

N2: =OFFSET($A$1,0,2+INT((2+ROW())/4),1,1)

Copy J2:N2 Down

Copy Columns J:N and paste as values


New Member

I just have one more modification for this - please?

I do not understand how to develop this for n-rows and n-columns.

Where Person Product Region Sales Date are fixed and in source table you get new dates periodicaly, so number of columns is growing.

I have problem understanding MOD function for getting right COLUMN number.

Thanks you rock!



Excel Ninja
Staff member

I have updated the formulas as they weren't quite right anyway and they will automatically adjust for up to 23 Columns of date data from Col D to Z

Shift your Headings to AA1:AE1

AA2: =OFFSET($A$1,INT(1+INT(ROW()-2)/COUNTA($D$1:$Z$1)),0,1,1)

AB2: =OFFSET($A$1,INT(1+INT(ROW()-2)/COUNTA($D$1:$Z$1)),1,1,1)

AC2: =OFFSET($A$1,INT(1+INT(ROW()-2)/COUNTA($D$1:$Z$1)),2,1,1)

AD2: =OFFSET($A$1,INT(1+INT(ROW()-2)/COUNTA($D$1:$Z$1)),3+MOD(ROW()-2,COUNTA($D$1:$Z$1)),1,1)

AE2: =OFFSET($A$1,0,3+MOD(ROW()-2,COUNTA($D$1:$Z$1)),1,1)

Copy AA2:AE2 down until you get to the end of your data