Convert Time table to single column

[JoshuaX1980]

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??
The file I have is an .out file by the way.

Kind Regards,

Lennart

 

[fairwinds]

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
	A	B	C	D	E	F	G	H	I	J
1	Dates	Values			Jan	Feb	Mar	Apr	Maj	Jun
2	2005-01-01	aaa1		1	aaa1	aaa2	aaa3	aaa4	aaa5	aaa6
3	2005-01-02	aaa10		2	aaa10	aaa11	aaa12	aaa13	aaa14	aaa15
4	2005-01-03	aaa19		3	aaa19	aaa20	aaa21	aaa22	aaa23	aaa24
5	2005-01-04	aaa28		4	aaa28	aaa29	aaa30	aaa31	aaa32	aaa33
6	2005-01-05	aaa37		5	aaa37	aaa38	aaa39	aaa40	aaa41	aaa42
7	2005-01-06	aaa46		6	aaa46	aaa47	aaa48	aaa49	aaa50	aaa51
8	2005-01-07	aaa55		7	aaa55	aaa56	aaa57	aaa58	aaa59	aaa60
9	2005-01-08	aaa64		8	aaa64	aaa65	aaa66	aaa67	aaa68	aaa69
10	2005-01-09	aaa73		9	aaa73	aaa74	aaa75	aaa76	aaa77	aaa78
11	2005-01-10	aaa82		10	aaa82	aaa83	aaa84	aaa85	aaa86	aaa87
12	2005-01-11	aaa91		11	aaa91	aaa92	aaa93	aaa94	aaa95	aaa96
13	2005-01-12	aaa100		12	aaa100	aaa101	aaa102	aaa103	aaa104	aaa105
Sheet1

 

[JoshuaX1980]

Thank you very much, it is working!!
ray:

 

[JoshuaX1980]

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]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1							Day
2					Year	Month	1	2	3	4	5	6	7	8	9	10	11
3	1961-01-01	10		19611	1961	1	10	11	12	13	14	15	16	17	18	19	20
4	1961-01-02	11		19612	1961	2	11	11	11	11	11	11	11	11	11	11	11
5	1961-01-03	12		19613	1961	3	12	11	10	9	8	7	6	5	4	3	2
6	1961-01-04	13		19614	1961	4	13	11	9	7	5	3	1	-1	-3	-5	-7
7	1961-01-05	14		19615	1961	5	14	11	8	5	2	-1	-4	-7	-10	-13	-16
8	1961-01-06	15		19616	1961	6	15	11	7	3	-1	-5	-9	-13	-17	-21	-25
9	1961-01-07	16		19617	1961	7	16	11	6	1	-4	-9	-14	-19	-24	-29	-34
10	1961-01-08	17		19618	1961	8	17	11	5	-1	-7	-13	-19	-25	-31	-37	-43
11	1961-01-09	18		19619	1961	9	18	11	4	-3	-10	-17	-24	-31	-38	-45	-52
12	1961-01-10	19		196110	1961	10	19	11	3	-5	-13	-21	-29	-37	-45	-53	-61
Sheet1

 

[JoshuaX1980]

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]

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]

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
	A	B	C	D	E	F	G	H	I
1					RAINFALL
2					YEAR	MONTH	1	2	3
3	1-1-1979	00-01-00		19791	1979	1	0.0	7.5	11.3
4	1-2-1979	07-01-00		19792	1979	2	0.0	0.0	0.0
5	1-3-1979	11-01-00		19793	1979	3	22.4	20.6	9.5
6	1-4-1979	00-01-00		19794	1979	4	0.0	0.0	0.0
7	1-5-1979	00-01-00		19795	1979	5	0.0	0.0	0.0
8	1-6-1979	00-01-00		19796	1979	6	0.0	0.0	0.0
9	1-7-1979			19797	1979	7	0.0	0.4	0.0
10	1-8-1979			19798	1979	8	0.0	0.0	0.0
11	1-9-1979			19799	1979	9	0.0	0.0	0.0
12	1-10-1979			197910	1979	10	0.0	0.0	0.0
13	1-11-1979			197911	1979	11	0.0	0.0	0.0
14	1-12-1979			197912	1979	12	0.0	0.0	6.3
15	1-13-1979			19801	1980	1	0.0	0.0	0.0
16	1-14-1979			19802	1980	2	0.0	2.5	0.0
17	1-15-1979			19803	1980	3	0.0	0.0	4.4
18	1-16-1979			19804	1980	4	0.0	0.0	0.0
19	1-17-1979			19805	1980	5	0.0	0.0	0.0
Matopos

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

Regards,
Lennart

 

[fairwinds]

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]

Ok thanks, Column B was a date ofcourse, now it is working!

Thanks a lot,
I owe you a beer

Lennart