Transforming data for Pivot analysis

[Lukas]

Hi,


can you help me with some data manipulation:

Problem:

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,

Lukas

 

[Hui]

Lukas

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

 

[Lukas]

Hi,

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

etc.


Hope this clarify my Q.


Thanks for help,

Luka

 

[Hui]

Luka

In J1:N1 put

Person     Product     Region    Sales    Date

as titles

then

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

 

[Lukas]

Great,

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!

Luka

 

[Hui]

Lukas,

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