How to reorganise spreadsheet into a list?

Question

I'd like to restructure the data stored in this Google Spreadsheet.

The first two columns show Year and Month, and the Days 1..31 are structured as the other columns

This then gives a table of rainfall data.

I'd like to create a list of Day/Month/Year with values for each

I don't know where to begin!

https://docs.google.com/spreadsheets/d/1T9KRtxTGIYyRy09Foqsd2pGSiLvGjcyupVjgiKb6Jeg/edit?usp=sharing

If someone has an Excel solution very happy to hear it also

Many thanks

Answer 1

Try this on a new sheet, cell A1:

=arrayformula({"Date","Rainfall";query(split(flatten(datevalue(Rainfall!A2:A&"-"&Rainfall!B2:B&"-"&Rainfall!C1:AG1)&char(9998)&Rainfall!C2:AG),char(9998)),"where Col2 >=0",0)})