Creating multiple rows and merging columns

Question

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.

Answer 1

This may need some alterations depending on your Excel version, and how your inputs/outputs are formatted.

For the first column:

=TOCOL(HSTACK(Table1[ID],Table1[ID],Table1[ID]))

For the second:

="PQ"&MOD(SEQUENCE(ROWS(B9#),,3),3)+1

, where B9 for me refers to the first cell of the new ID column.

For the third column:

=IFS(C9#="PQ1", XLOOKUP(B9#, Table1[ID], Table1[P1]), C9#="PQ2", XLOOKUP(B9#, Table1[ID], Table1[P2]), C9#="PQ3", XLOOKUP(B9#, Table1[ID], Table1[P3]))

, where C9 refers to the first cell of the "A" column.

Similarly, for the last column:

=IFS(C9#="PQ1", XLOOKUP(B9#, Table1[ID], Table1[Q1]), C9#="PQ2", XLOOKUP(B9#, Table1[ID], Table1[Q2]), C9#="PQ3", XLOOKUP(B9#, Table1[ID], Table1[Q3]))

As I said, this might need some alterations, but let me know if so.

Answer 2

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:

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.