How can Excel achieve this transform function?

Question

Is there a way to easily transform the first table into the second one in Excel? Thanks.&nbsp;&nbsp;ABX1Y1&nbsp;X1Y2&nbsp;X1Y3&nbsp;X2Y4&nbsp;X2Y5&nbsp;X2Y6&nbsp;X2Y7&nbsp;X2Y8&nbsp;X3Y9&nbsp;X3Y10&nbsp;X3Y11&nbsp;X3Y12&nbsp;X3Y13&nbsp;X1&nbsp;Y14X1&nbsp;Y15X1&nbsp;Y16X2&nbsp;Y17X2&nbsp;Y18X2&nbsp;Y19X2&nbsp;Y20X2&nbsp;Y21X3&nbsp;Y22X3&nbsp;Y23X3&nbsp;Y24X3&nbsp;Y25X3&nbsp;Y26&nbsp;&nbsp;A&nbsp;&nbsp;&nbsp;&nbsp;B&nbsp;&nbsp;&nbsp;&nbsp;X1Y1Y2Y3&nbsp;&nbsp;Y14Y15Y16&nbsp;&nbsp;X2Y4Y5Y6Y7Y8Y17Y18Y19Y20Y21X3Y9Y10Y11Y12Y13Y22Y23Y24Y25Y26

alannavarro · Accepted Answer

Yanbo_JIANG&nbsp;&nbsp;Hello!&nbsp;Attached is an excel file, I did this with 3 different ways power query, a pivot table or dynamic formulas.&nbsp;&nbsp;Hope it works.&nbsp;&nbsp;

Forum Discussion

How can Excel achieve this transform function?

2 Replies

Resources

	A	B
X1	Y1
X1	Y2
X1	Y3
X2	Y4
X2	Y5
X2	Y6
X2	Y7
X2	Y8
X3	Y9
X3	Y10
X3	Y11
X3	Y12
X3	Y13
X1		Y14
X1		Y15
X1		Y16
X2		Y17
X2		Y18
X2		Y19
X2		Y20
X2		Y21
X3		Y22
X3		Y23
X3		Y24
X3		Y25
X3		Y26

	A					B
X1	Y1	Y2	Y3			Y14	Y15	Y16
X2	Y4	Y5	Y6	Y7	Y8	Y17	Y18	Y19	Y20	Y21
X3	Y9	Y10	Y11	Y12	Y13	Y22	Y23	Y24	Y25	Y26

	A	B
X1	Y1
X1	Y2
X1	Y3
X2	Y4
X2	Y5
X2	Y6
X2	Y7
X2	Y8
X3	Y9
X3	Y10
X3	Y11
X3	Y12
X3	Y13
X1		Y14
X1		Y15
X1		Y16
X2		Y17
X2		Y18
X2		Y19
X2		Y20
X2		Y21
X3		Y22
X3		Y23
X3		Y24
X3		Y25
X3		Y26

	A					B
X1	Y1	Y2	Y3			Y14	Y15	Y16
X2	Y4	Y5	Y6	Y7	Y8	Y17	Y18	Y19	Y20	Y21
X3	Y9	Y10	Y11	Y12	Y13	Y22	Y23	Y24	Y25	Y26

	A	B
X1	Y1
X1	Y2
X1	Y3
X2	Y4
X2	Y5
X2	Y6
X2	Y7
X2	Y8
X3	Y9
X3	Y10
X3	Y11
X3	Y12
X3	Y13
X1		Y14
X1		Y15
X1		Y16
X2		Y17
X2		Y18
X2		Y19
X2		Y20
X2		Y21
X3		Y22
X3		Y23
X3		Y24
X3		Y25
X3		Y26

	A					B
X1	Y1	Y2	Y3			Y14	Y15	Y16
X2	Y4	Y5	Y6	Y7	Y8	Y17	Y18	Y19	Y20	Y21
X3	Y9	Y10	Y11	Y12	Y13	Y22	Y23	Y24	Y25	Y26