offset() to transpose horizontal hourly data to vertical hourly data?

Question

I have a table of hourly data for an extended number of days.  The table currently has each day in a row:

	HE01	HE02	...	HE24
1/1/2015	25	22	24	23
1/2/2015	17	19	20	18
...
1/31/2015	13	14	15	16

I would like to create the data in a vertical format:

1/1/2015	HE01	25
1/1/2015	HE02	22
...	...	...
1/1/2015	HE24	23
...	...	...
1/31/2015	HE01	13
1/31/2015	HE02	14
...	...	...
1/31/2015	HE24	16

Answer 1

Let's say the first table is on Sheet1 in A1:Y32.

On Sheet 2, enter the following formulas in A2, B2 and C2, respectively:

=INDEX(Sheet1!$A$2:$A$32,(ROW()-2)/24+1)

=INDEX(Sheet1!$B$1:$Y$1,MOD(ROW()-2,24)+1)

=INDEX(Sheet1!$B$2:$Y$32,(ROW()-2)/24+1,MOD(ROW()-2,24)+1)

Format A2 as a date if Excel doesn't do so automatically.

Select A1:C1, then fill down to row 745.

Answer 2

Thank you, Hans.  That worked!

Answer 3

Another option:

Presuming the first table is on sheet named Data_H in A1:Y32:

In another sheet (leaving cell A1 blank), enter below formulas in cells A2, B2 & C2 respectively,

format A2 as date, & copy down to row 745 in the 3 columns:

cell A2: =IF(A1="",Data_H!$A$2,IF(COUNTIF($A$1:$A1,A1)<24,$A1,$A1+1))

cell B2: =IF(A2=A1,LEFT(B1,2)&TEXT(RIGHT(B1,2)+1,"00"),Data_H!$B$1)

cell C2: =INDEX(Data_H!$A$1:$Y$32,MATCH(A2,Data_H!$A$1:$A$32,0),MATCH(B2,Data_H!$A$1:$Y$1,0))

Rgds,

Amit Tandon

Answer 4

Hi,

You may refer to my solution at this link - http://www.ashishmathur.com/converting-a-matrix-data-layout-to-a-tabular-layout/.

Hope this helps.