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

Question

Hello everyone,&nbsp;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 &amp; 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

oliverscheurich · Answer

Patrick002320&nbsp;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.

sergeibaklan · Answer

Patrick002320&nbsp;
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]])
    )
)

patrick2788 · Answer

Patrick002320&nbsp;PQ is the best solution with a large data set.&nbsp;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)))
        )
    )
)

Forum Discussion

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

3 Replies

Resources