Data arrangement

Dear all,
I need a help to arrange my climate data. I have 30 years of climate data as you can see in the data sheet attached and I want the data to be arranged monthly as you can see in format sheet.

So you want the 31 days worth of data from January 1975 to appear in the first 31 cells of column B of the format sheet, then the data from 1976 to appear in the next 31 days, and so on for all the years? That would mean, though, that the data for February would quickly get out of alignment, as there are fewer days in that month, and the same would happen for the four 30-day months. Is that what you want?

Pete

yes Pete that is what I want. I know the data length for each month will be different.

It is much different. I want to have all the data for January in one column, February and so on. I am doing a bias correction that demand individual months.

Pete, I can also take each months in a separate worksheet. forinstance, Januarys in sheet 2, Februarys in sheet 3 and so on to avoid any alignment issues. Thanks

Thanks, where I my copying the script to? can you use my data and attached it so that I can see more clearly? I have limited knowledge in coding

I've been struggling to get one composite formula that you could put into B2 of the Format sheet and just copy across and down. I imagine this is a one-off, so I think the simplest approach is to put this formula in B2:

=INDEX(Data!$B$2:$AF$32,MOD(ROWS($1:1)-1,31)+1,INT((ROWS($1:1)-1)/31)+1)

This can be copied down to the bottom, and it will return all the January data.

You can also copy this across into C2:M2, but before copying it down each column, you will need to make a few changes as shown in colour. The blue-coloured numbers are the number of days in each month, so for March, May, July, August, October and December you can leave those as 31, but for April, June, September and November you should change them to 30 (February is detailed below). You also need to change the range for each month, according to what is used on the Data sheet, i.e. the red numbers. January data covers rows 2 to 32 inclusive, February is in rows 34 to 62, March is 64 to 94, and so on. Once you have made these changes then you can copy the formulae down.

February is unusual because it has a differing number of days, depending on whether it is a leap year or not. The easiest approach to this is to set the blue numbers to 28, but that would mean that you would miss the data for the 29th when it occurs. It might be easier to just put that in manually, rather than devise a complicated formula to just pick up one item of data every 4 years.

Hope this helps.

Pete

Only after I posted mine, as I was trying for quite some time to come up with a way to have MOD(..28) for 28 rows and then MOD(..29) for the next 29 rows, then back to MOD(..28) for the next 84 rows for February, and then gave up and opted for the simpler approach (pragmatist). I was trying to avoid using a helper with this one, although I often do that in my solutions.

Pete

Thanks peter. This work only that it didn't take care of the leap year.

I copy the formula but it doesn't return any value. Do you mean i need to insert rows in the data sheet?

Originally Posted by akumagaavese

... it didn't take care of the leap year...

If you use 29 for the divisor for February (with the appropriate lookup range) then you will get zero for those years that are not leap years. It is easy to identify these and just delete those values.

Hope this helps.

Pete