Convert Time table to single column

JoshuaX1980

Board Regular
Joined
Jan 26, 2005
Messages
80
Hi,
I want to use the PCRaster model for my hydrological research. I received flow data of a river in a format that can not be used in PCRaster. The daily data per year is presented as follows:
The first column consists of day 1 to 31, the next column is January, COlumn C is February, etc until December. Because January has 31 days all cells are filled with the daily value for January. Only 28 rows are filled for February, because there are only 28 days in Feb., etc.

e.g. 2004
Jan Feb Mar ... Dec
1 x x x .... x
2 x x x .... x
.
.
28 x x x x
29 x - x x
.
31 x - x x

I want to have one long single column containing all the flow data consecutive for the year 2004, like:
1 Jan 2004 x
2 Jan 2004 x
...
31 Dec 2004 x

Does anybody have an idea how to do this?? :rolleyes:
The file I have is an .out file by the way.

Kind Regards,

Lennart
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Welcome to the board!

Try:

B2: =INDEX($E$2:$P$32,MATCH(DAY(A2),$D$2:$D$32,0),MATCH(TEXT(A2,"MMM"),$E$1:$P$1,0))

Drag down.
Book1
ABCDEFGHIJ
1DatesValuesJanFebMarAprMajJun
22005-01-01aaa11aaa1aaa2aaa3aaa4aaa5aaa6
32005-01-02aaa102aaa10aaa11aaa12aaa13aaa14aaa15
42005-01-03aaa193aaa19aaa20aaa21aaa22aaa23aaa24
52005-01-04aaa284aaa28aaa29aaa30aaa31aaa32aaa33
62005-01-05aaa375aaa37aaa38aaa39aaa40aaa41aaa42
72005-01-06aaa466aaa46aaa47aaa48aaa49aaa50aaa51
82005-01-07aaa557aaa55aaa56aaa57aaa58aaa59aaa60
92005-01-08aaa648aaa64aaa65aaa66aaa67aaa68aaa69
102005-01-09aaa739aaa73aaa74aaa75aaa76aaa77aaa78
112005-01-10aaa8210aaa82aaa83aaa84aaa85aaa86aaa87
122005-01-11aaa9111aaa91aaa92aaa93aaa94aaa95aaa96
132005-01-12aaa10012aaa100aaa101aaa102aaa103aaa104aaa105
Sheet1
 

JoshuaX1980

Board Regular
Joined
Jan 26, 2005
Messages
80
Hi,
I posted this question a few days ago (Convert Time Table to single Column), i received a few replies but they didn't work out. Actually it is a similar question you replied on, so I thought to ask you again. I copy paste the question below. The only difference between the question you answered and this one is that the days and Months are shifted.
I Hope you can help me.

Hi,
A similar question as a few days ago "Convert Time Table to single Column". I have daily data per year presented as follows:
The first column (A) consists of the year 1961 the second (B)is the month and column C to AJ represent day 1 to 31. So after 12 rows the first column changes to 1962, and column B start counting from month 1 to month 12 again, Columns C up to AJ show daily data for the year 1962.

e.g. 1961
Year |Month |day 1 - 31
1961 1 x x x x x. .x x
1961 2 x x x x . .x
....
1998 6 x x x ....x x


I want to have one long single column containing all the data consecutive per year e.g. 1961, like:
1 Jan 1961 x
2 Jan 1961 x
...
31 Dec 1998 x

I know it has something to do with =INDEX( bla bla bla) but I could not figure it out. Anybody?

Kind Regards,

Lennart
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638

ADVERTISEMENT

I would do something like this:

Create the date range by typing the first date in a3 and drag down.

Then:

B3: =INDEX($G$3:$AK$1912,MATCH(YEAR(A3)&MONTH(A3),$D$3:$D$1000,0),MATCH(DAY(A3),G$2:$AK$2,0))

D3: =E3&F3

And drag these down too.
Book1
ABCDEFGHIJKLMNOPQ
1Day
2YearMonth1234567891011
31961-01-011019611196111011121314151617181920
41961-01-021119612196121111111111111111111111
51961-01-0312196131961312111098765432
61961-01-04131961419614131197531-1-3-5-7
71961-01-051419615196151411852-1-4-7-10-13-16
81961-01-06151961619616151173-1-5-9-13-17-21-25
91961-01-07161961719617161161-4-9-14-19-24-29-34
101961-01-0817196181961817115-1-7-13-19-25-31-37-43
111961-01-0918196191961918114-3-10-17-24-31-38-45-52
121961-01-101919611019611019113-5-13-21-29-37-45-53-61
Sheet1
 

JoshuaX1980

Board Regular
Joined
Jan 26, 2005
Messages
80
Hi, thanks for your fast reply, but Cell B3 says #N/A
I think it has something to do with column D, shouldn't that column correspond to column A (same year same DAY). Now it counts Months instead of days, I tried to change it to days, so column A corresponds with C, but this didn't work either.
I did change the formula saying :$AK$1912 to $AK$235 because my data goes from 1-1-1979 to 31-12-2001. Same for changing $D$1000,0) to $D$8404

My Excel sheet says Day 1, Day 2, etc instead of 1,2,3, etc I changed that, but that didn't work either.

Do you know what is wrong?

Lennart[/img]
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638

ADVERTISEMENT

No,

Column D is day and year concatenated.

Make sure column E holds year as 4 digits, not a date.
Col F a number 1 to 12
And row 2 a number 1 to 31

All formatted as numbers.


Col A should be a true date.

You can highlight different parts of the formula by highlighting and press F) to see wich part fails.
 

JoshuaX1980

Board Regular
Joined
Jan 26, 2005
Messages
80
But the year and Month get concatenated, not the year and day. This is what shows up when i fill in for B3=INDEX($G$3:$AK$235,MATCH(YEAR(A3)&MONTH(A3),$D$3:$D$8404,0),MATCH(DAY(A3),G$2:$AK$2,0))
Rain Bula_Mat_Umzingw and missing d vb3.xls
ABCDEFGHI
1RAINFALL
2YEARMONTH123
31-1-197900-01-0019791197910.07.511.3
41-2-197907-01-0019792197920.00.00.0
51-3-197911-01-00197931979322.420.69.5
61-4-197900-01-0019794197940.00.00.0
71-5-197900-01-0019795197950.00.00.0
81-6-197900-01-0019796197960.00.00.0
91-7-197919797197970.00.40.0
101-8-197919798197980.00.00.0
111-9-197919799197990.00.00.0
121-10-19791979101979100.00.00.0
131-11-19791979111979110.00.00.0
141-12-19791979121979120.00.06.3
151-13-197919801198010.00.00.0
161-14-197919802198020.02.50.0
171-15-197919803198030.00.04.4
181-16-197919804198040.00.00.0
191-17-197919805198050.00.00.0
Matopos


Everything is Number format now, but I still have the problem.

Regards,
Lennart
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Year and month should get concatenated!

Justa typo in my second post.


I think everything is OK in your exhibit. Just format B column as general and not date!
 

JoshuaX1980

Board Regular
Joined
Jan 26, 2005
Messages
80
Ok thanks, Column B was a date ofcourse, now it is working!

Thanks a lot,
I owe you a beer (y)

Lennart
 

Watch MrExcel Video

Forum statistics

Threads
1,122,694
Messages
5,597,571
Members
414,157
Latest member
MrBJBones

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