Converting a column of data into multiple columns

funkystuu

New Member
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.

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
You will probably get a better response if you post a sample of your data.

HTH

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
ABCDEFG
2195919601961etc
301/01/19591.349
402/01/19591.221
503/01/19591.088
604/01/19590.997
705/01/19590.935
806/01/19590.884
907/01/19590.657
1008/01/19590.683
1109/01/19590.686
1210/01/19590.666
1311/01/19590.657
shermanbridge

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

Many thanks for your help this worked fine.

Cheers Stu

Replies
8
Views
220
Replies
2
Views
469
Replies
3
Views
506
Replies
1
Views
338
Replies
3
Views
386

1,219,792
Messages
6,150,290
Members
450,949
Latest member
faizanmalik10

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back