Converting a column of data into multiple columns

[funkystuu]

I have a column of river flow data ordered by date which is around 15,000 rows long (daily measurements from 1959 - 2002).

However, I need to get the column split with one column for each year as opposed to one big column. Is there a quicker way than manually moving the data to new columns? I thought of possibly using an "offset" but with irregular numbers of days in leap years that does not work.

Many thanks for any help anyone could offer.

 

[SteveO59L]

You will probably get a better response if you post a sample of your data.

HTH

 

[funkystuu]

Ok thanks here is an image of the sheet. Colums A and B contain data down to row 15667. I am trying to split the data into individual yrs - so there is 365 days per coulmn in the simplest way possible.

Many Thanks Stuart

shermanbridge.xls
	A	B	C	D	E	F	G
2				1959	1960	1961	etc
3	01/01/1959	1.349
4	02/01/1959	1.221
5	03/01/1959	1.088
6	04/01/1959	0.997
7	05/01/1959	0.935
8	06/01/1959	0.884
9	07/01/1959	0.657
10	08/01/1959	0.683
11	09/01/1959	0.686
12	10/01/1959	0.666
13	11/01/1959	0.657
shermanbridge

 

[cherria]

Ok, here is my best suggestion, others will surely have better.

In column C add acolumn and call it year. In this column put the formula

=year(A3) and copy it to the end of your table

Add another column and call it day and in column D put the formula

=A3-DATE(YEAR(A3),1,1)+1 and copy this to the end

you now have 2 columns one is the year for the data and one is the day number

now create a pivot table of your 4 columns of data

Data->pivot table

On the left hand exis put the day field
on the top axis put the year field
and in the detail, put sum of (flow field)

the resulting pivot table should give you what you want

 

[funkystuu]

Many thanks for your help this worked fine.

Cheers Stu