Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

1

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

2

share|improve this question
1  
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

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

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.