How can I combine data from multiple columns & rows into one row/column

[Patrick002320]

Hello everyone,

I have never done this before and I am very new to excel but I have a table of data about monthly temperatures for each year. In the A column I have all the years from 1961-2023 and then in their corresponding row the values for all 12 months I have a picture included. What I would like to do is to in a way transpose each 12 month period but at once? If that even makes sense. So in one row/column I would like all the data points in order of year & month. So 1961 jan-dec, then directly following in the same row 1962 then 1963 etc. without having to transpose each year separately. Hope this is clear thanks for any suggestions

[#2]

@Patrick002320 

An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.

[#3]

@Patrick002320 

As variant, if on Excel 365

=VSTACK(
    {"Year"," Month","Temperature"},
    HSTACK(
        TOCOL(IF(SEQUENCE(, 12), Tabelle1[Year])),
        TOCOL(
            IF(
                SEQUENCE(ROWS(Tabelle1)),
                Tabelle1[[#Headers],[January]:[December]]
            )
        ),
        TOCOL(Tabelle1[[January]:[December]])
    )
)

[#4]

@Patrick002320 

PQ is the best solution with a large data set.

Here's another way with a formula:

=REDUCE(
    {"Year", "Month", "Val"},
    year,
    LAMBDA(a, v,
        LET(
            values, TOCOL(FILTER(data, year = v)),
            VSTACK(a, HSTACK(EXPAND(v, 12, , v), TOCOL(months), TOCOL(values)))
        )
    )
)