# Convert Time table to single column

#### JoshuaX1980

##### Board Regular
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

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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

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

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

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

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]

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.

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

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!

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

Thanks a lot,
I owe you a beer

Lennart

Replies
7
Views
657
Replies
2
Views
312
Replies
11
Views
1K
Replies
3
Views
107
Replies
0
Views
236

1,217,440
Messages
6,136,635
Members
450,022
Latest member
Joel1122331

### 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.

### Which adblocker are you using?

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