Transform data in Excel

Question

I have two problems with my data in Excel.

1. I have this table, so for every day in the year I have got 24 values, that´s 365 columns.

image1 http://imageshack.us/a/img600/1773/dataxz.jpg

I need to write a macro, that transform the data vertically, below each other. Like in the other table. So the 1.1.1996 is in (B4:B28), 2.1.1996 (B29:B53) etc. I made a simple macro but I don´t know how to continue with it automatically.

2. Second problem is the pivot table, or, how to easy make for example daily/weekly/monthly averages or sums of every columns separately. And than the graph. I made one table but it makes me wrong average

image2 http://imageshack.us/a/img690/5305/pivottable.png

Answer 1

You can do the transposing without any macro:

The direct way for your first step would be to use the TRANSPOSE function, i.e. select a range that is 365rowsx24cols and enter =TRANSPOSE(B4:NB27) and press Ctrl+Shift+Enter.

However, I'd recommend to address problem 1 and 2 in a slightly different manner: Translate your data to a list, i.e. a table with 365x24 rows x 3 columns: Date, Hour, Value. This table can then be easily be used as the source for a pivot table!

To do so, you could either use two helper columns - the first one starting at 1 and increasing every row by one until it's 24 then reset (In A2: 1, in A3: =IF(A2=24,1,A2+1)), the second starting with 1 and increasing by 1 every 24 rows (B2: 1, B3: =IF(A3=1,B2+1,B2). Alternatively, you could simply use the ROW() function (which returns you the row of the cell with the formula if no parameter is provided) to derive the number 1 to 24 with MOD(ROW(),24)-1 and the 1 to 365 with INT((ROW()-2)/24)+1. (The +1 and -2 is to adjust for the data table starting in row 2).

Using the INDEX function with those two parameters, you can now easily retrieve the data for the 3 columns:

Date: =INDEX($B$3:$NB$3,B2) (or without the helper columns: =INDEX($B$3:$NB$3,INT((ROW()-2)/24)+1))

Hour: =INDEX($A$4:$A$27,A2)(or =INDEX($A$4:$A$27,MOD(ROW(),24)-1))

Value: =INDEX($B$4:$NB$27,A2,B2)(or =INDEX($B$4:$NB$27,MOD(ROW(),24)-1),INT((ROW()-2)/24)+1))