Converting a column of data into multiple columns

funkystuu

New Member
Joined
Jan 25, 2005
Messages
3
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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,899
You will probably get a better response if you post a sample of your data.

HTH


:cool:
 

funkystuu

New Member
Joined
Jan 25, 2005
Messages
3
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
 

cherria

Well-known Member
Joined
Nov 17, 2004
Messages
708
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
 

Forum statistics

Threads
1,148,053
Messages
5,744,531
Members
423,881
Latest member
Nguyen Vu

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top