Excel_macro_pivot table_analyzing data

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.

I need to write a macro, that transform the data vertically, bellow 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.

1. 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 :(

-
You're dealing with two concepts here: a looping macro, and chart/graphing techniques. Split this into two questions, and show your macro so far for the first question, and your table/graph so far for the second. –  mcalex Mar 10 '13 at 10:55
Range("AB4:AB27").Select Selection.Copy Range("B28").Select ActiveSheet.Paste Range("B4:B27").Select –  Peter Mar 13 '13 at 15:20

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)`)

-
thanks, but I made a mistake. I need to put the hourly values for the days in one column. So I have column A with date and hours, or A for dates, B for hours and C for values. Some macro like this but without certain number and loop until last column Range("AB4:AB27").Select Selection.Copy Range("AA28").Select ActiveSheet.Paste Range("AC4:AC27").Select Selection.Copy Range("AA52").Select ActiveSheet.Paste –  Peter Mar 13 '13 at 15:14