Row per year to month per row

Here is some rough excel code. It works.
NOTE that the excel sheet must be saved as a .XLSM macro enabled worksheet and macros will have to be enabled.

excel7
click developer tab
type a new macro name
e. g. YearPerRowToClmn

Assign to keybord if you like

click [create]
then between:

Sub YearPerRowToClmn()

End Sub

paste this (but not the “———”:

‘——————————
ActiveCell.Offset(1, 0).Range("A1:A11").Select
Application.CutCopyMode = False
Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(-1, 2).Range("A1:K1").Select
Selection.Copy
ActiveCell.Offset(1, -1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveCell.Offset(11, -1).Range("A1").Select

repeattranspose:
If Len(ActiveCell.Text) < 2 Then GoTo stopp
ActiveCell.Offset(1, 0).Range("A1:x11").Select

Application.CutCopyMode = False
Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(-1, 2).Range("A1:K1").Select
Selection.Copy
ActiveCell.Offset(1, -1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveCell.Offset(11, -1).Range("A1").Select
GoTo repeattranspose

stopp:
'———————–

to use:
get data in text form with 1 year of 12 months + other stuff
Select data [ctrl]+a selects the lot
Copy data [ctrl+c
open blank sheet in the workbook containing the macro
paste the data at cell a1
You now have a single column of data one year per row. If your excel is set up differently it may convert the data to columns automatically, ifnot
select the first to last year:
click first - scroll to last and click the last year whilst holding shift
select [data] tab
click text to colums
click delimited if you KNOW that there is always a certain character (space, comma etc) between monthly data
or click fixed width
click next (select delimiter character if necessary)
check the colums are correctly selected – move, delete or add. If station number is in data this usually has the date attached without space. If so add a separator to separate the date from the station.
If station number is in first column click next and select station number to be text click finish
else click finish
You should now have the data separated into columns.

Click the cell containing the first date (or the first cell to the left of january’s temperature)
Save the workbook as the next step is not undo-able.
Run the macro above (use keyboard shortcut or go to the developer tab and double click the macro name.
The macro should stop on the last line of data (looks for empty cell). However if it does not press [ctrl]+[break] a number of times. select end or debug from the options according to taste.
No guarantee is given with this software!!!!!

If you ever end up with a correctly formatted column of monthly data you will need to remove all data error indicators.
Mark the data column
on [data] tab select filter
on first line of column click the down arrow
deselect all (click the ticked select all box)
look through offered numbers for error indicators "-" "-9999.9" etc.
click the boxes associated with the error indicators
press[ok]
only the data in error is now shown
mark it all (be careful of the first box as this is patially obscured by the arrow)and press [delete]
Turn off filter by clicking filter in the ribbon again
Data is now clean
I data shows temp*10
the adjacent (right) to the first temp type
= [click temperature to left to enter cell]/10
mark all column including this cell to last row containing temperature
from [home] tab click fill then select fill down
This column now contains correctly scaled temperature but the cell contents are actually formulae.
with column marked copy column [ctrl]+c
right click first cell of this column and select paste special
select values only then ok it
The column now contains actual values and can therfore be copied to another sheet with dates in decimal format i.e. year+(month-1)/12. Note that excel does not like true dates before jan 1st 1900