I have got 365 colums as dates and 24 rows as hours and data for each hour of the year. How do I transpose all this data into three colums with ascending dates and each day with ascending hours?

Thus how do I get B1 to place itself at A25, B2 at A26 ... C1 at A49, C2 at A50 and so on?

Hi, 1. Does your worksheet have any headings for dates or for hours.

2. You want the 365 columns converted into three columns.

At what date/hour do the first two columns stop, i.e what are the transition points and is there any logic to where those transition points occur.I don't know how you intend to use your data, but moving from a logical array of 365*24 to three columns which 'mix' dates and hours, will make data analysis a lot harder.

Imaging trying to get an average value for 10:00 AM for the year

In your current arrangement you would use a simple formula such as =AVERAGE(A10:NA10),

but with the data split into three columns, It would require a complex formula to get the same result.You could consider putting each month's data onto a separate worksheet in the same workbook, if for instance you needed compatibility with Excel 2003 (only 256 columns available).

Regards

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History