Hello,
I have a long list of data in one vertical column. It is a data value for every hour of every day of a year. I.E
------------------------------------
B C
1/1/2007 (hour 1) (data)
1/1/2007 (hour 2) (data)
1/1/2007 (hour 3) (data)
1/1/2007 (hour 4) (data)
.
.
12/31/2007 (hour 22) (data)
12/31/2007 (hour 23) (data)
12/31/2007 (hour 24) (data)
--------------------------------------------
I need to put the data in a format so each day has its own row with the data values to the right of the date. I.E.
-------------------------------------------------
R S T U AP 1/1/2007 (hour 1 data) (hour 2 data) (hour 3 data) ............ (hour 24 data)
1/2/2007 (hour 1 data) (hour 2 data) (hour 3 data) ............ (hour 24 data)
1/3/2007 (hour 1 data) (hour 2 data) (hour 3 data) ............ (hour 24 data)
.
.
.
12/31/2007 (hour 1 data) (hour 2 data) (hour 3 data) ......... (hour 24 data)
--------------------------------------------------
What is the best way to go about this? I posted the actual column numbers if anyone wants to write some actual code I can try. Any help would be appreciated otherwise I will be doing a whole lot of paste special. Thank you in advance!
Jake
Hi Jake,
In cell R1 put the date 1/1/08 (or whatever year you're starting with). Fill that down to row 366 (leap year!).
In cell S1 put the formula:
Fill that formula right to column AP and down to your last row. Hopefully that does what you need.=OFFSET($C$1,(ROW(A1)*24-24)+(COLUMN(A1)-1),0)
Paul,
That worked great. Thank you for your help, it saved me a lot of time!
Jake
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks