Excel help! Transposing selections

[Goldy]

Hi,

I am trying to move some data around which involves transposing columns into rows. Theres more...

What I have is 2 very long columns, Column A contains a date in 2012, e.g. 04/11/2012, and column B contains a value for that date. However there are 24 rows for each date in the year, and the adjacent cell contains a value for each hour in the day.

What I want to do is copy the 24 values in row B for each day of the year and transpose them into rows.

So I start with this:

01/01/2012, 5
01/01/2012, 3.5
01/01/2012, 6.2
...
02/01/2012, 4
02/01/2012, 7.5

And I somehow turn it into this:

01/01/2012, 5, 3.5, 6.2
02/01/2012, 4, 7.5,
etc etc

Thanks for any assistance! Racking my brains over this one, really dont want to have to select, copy and transpose paste 365 blocks of data. Thanks!

 

[gillywibble]

Copy the items you want then paste special and select 'Transpose'.

I'll do it for you if you want.

Edit: Read your post properly this time It can be done easily by writing a simple Macro which again I could put together for you

 

[Malakite]

I would use a pivot table to do this.

Have the dates in the row fields and the numbers in both the column and value fields.

This will give you the table you want but with some blanks between numbers. to get rid of these copy into another sheet. highlight the table and hit F5, hit the button that says 'special' then highlight 'blanks'. This will select only the blank cells which you can then delete.