Search code examples
exceltranspose

Creating multiple rows and merging columns

I have the following input data:

ID P1 P2 P3 Q1 Q2 Q3
1 1 2 3 4 5 6
2 10 20 30 40 50 60
3 1 2 30 400 50 60

And I want to change it to:

ID A P Q
1 PQ1 1 4
1 PQ2 2 5
1 PQ3 3 6
2 PQ1 10 40
2 PQ2 20 50
2 PQ3 30 60
3 PQ1 1 400
3 PQ2 2 50
3 PQ3 30 60

So multiple rows for each ID, with one column stating the number behind P and Q columns, and then two columns with the corresponding values.

Solution

  • Assuming no excel version constraints as per the tags listed in the question, you can try the following array formula, which spills the entire result including the header:

    =LET(gr,3, in, A2:G4, pqs, B1:G1, ids, TAKE(in,,1), data, DROP(in,,1),
 t, TRANSPOSE(WRAPROWS(pqs,gr)), A, LEFT(INDEX(t,,1)) & INDEX(t,,2),
 REDUCE({"ID","A","P","Q"}, ids, LAMBDA(ac,i, LET(r, SEQUENCE(gr,,i,0),
  VSTACK(ac, HSTACK(r, A, TRANSPOSE(WRAPROWS(INDEX(data,i),gr))))))))

    Here is the output: output

    We define the length of each group under the variable gr (in our case is 3). The main idea is to use WRAPROWS function to wrap rows by gr. We use it for generating the A, P, and Q columns. SEQUENCE is used to generate the column ID. The rest is just to use REDUCE/VSTACK-HSTACK pattern as it is documented in my answer to the following question: how to transform a table in Excel from vertical to horizontal but with different length.

    In order to easily maintain the formula, only in and pqs names depend on the input range, the rest are names generated from them, using the DROP and TAKE functions.