SOLVED

How can Excel achieve this transform function?

%3CLINGO-SUB%20id%3D%22lingo-sub-3129814%22%20slang%3D%22en-US%22%3EHow%20can%20Excel%20achieve%20this%20transform%20function%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3129814%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20a%20way%20to%20easily%20transform%20the%20first%20table%20into%20the%20second%20one%20in%20Excel%3F%20Thanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22192%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%20height%3D%2219%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EB%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EX1%3C%2FTD%3E%3CTD%3EY1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EX1%3C%2FTD%3E%3CTD%3EY2%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3EX1%3C%2FTD%3E%3CTD%3EY3%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EX2%3C%2FTD%3E%3CTD%3EY4%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EX2%3C%2FTD%3E%3CTD%3EY5%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EX2%3C%2FTD%3E%3CTD%3EY6%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EX2%3C%2FTD%3E%3CTD%3EY7%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EX2%3C%2FTD%3E%3CTD%3EY8%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EX3%3C%2FTD%3E%3CTD%3EY9%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EX3%3C%2FTD%3E%3CTD%3EY10%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EX3%3C%2FTD%3E%3CTD%3EY11%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EX3%3C%2FTD%3E%3CTD%3EY12%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EX3%3C%2FTD%3E%3CTD%3EY13%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EX1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EY14%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EX1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EY15%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EX1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EY16%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EX2%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EY17%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EX2%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EY18%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EX2%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EY19%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EX2%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EY20%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EX2%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EY21%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EX3%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EY22%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EX3%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EY23%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EX3%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EY24%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EX3%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EY25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3EX3%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EY26%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22704%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%20height%3D%2219%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EB%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EX1%3C%2FTD%3E%3CTD%3EY1%3C%2FTD%3E%3CTD%3EY2%3C%2FTD%3E%3CTD%3EY3%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EY14%3C%2FTD%3E%3CTD%3EY15%3C%2FTD%3E%3CTD%3EY16%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EX2%3C%2FTD%3E%3CTD%3EY4%3C%2FTD%3E%3CTD%3EY5%3C%2FTD%3E%3CTD%3EY6%3C%2FTD%3E%3CTD%3EY7%3C%2FTD%3E%3CTD%3EY8%3C%2FTD%3E%3CTD%3EY17%3C%2FTD%3E%3CTD%3EY18%3C%2FTD%3E%3CTD%3EY19%3C%2FTD%3E%3CTD%3EY20%3C%2FTD%3E%3CTD%3EY21%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3EX3%3C%2FTD%3E%3CTD%3EY9%3C%2FTD%3E%3CTD%3EY10%3C%2FTD%3E%3CTD%3EY11%3C%2FTD%3E%3CTD%3EY12%3C%2FTD%3E%3CTD%3EY13%3C%2FTD%3E%3CTD%3EY22%3C%2FTD%3E%3CTD%3EY23%3C%2FTD%3E%3CTD%3EY24%3C%2FTD%3E%3CTD%3EY25%3C%2FTD%3E%3CTD%3EY26%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3129814%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

Is there a way to easily transform the first table into the second one in Excel? Thanks.

 

 AB
X1Y1 
X1Y2 
X1Y3 
X2Y4 
X2Y5 
X2Y6 
X2Y7 
X2Y8 
X3Y9 
X3Y10 
X3Y11 
X3Y12 
X3Y13 
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    
X1Y1Y2Y3  Y14Y15Y16  
X2Y4Y5Y6Y7Y8Y17Y18Y19Y20Y21
X3Y9Y10Y11Y12Y13Y22Y23Y24Y25Y26
2 Replies
best response confirmed by Yanbo_JIANG (New Contributor)
Solution

@Yanbo_JIANG 

 

Hello! 

Attached is an excel file, I did this with 3 different ways power query, a pivot table or dynamic formulas. 

 

Hope it works.

 

 

Appreciated! Learned a lot!