How to transpose in excel ?

[contactshubhamyadav]

Hello everyone,

I am new to this forum and learning excel.

I need some help in excel for transposing cells as shown in the screenshot.

Please can someone help?

Best regards,
Shubham

 

[bosco_yip]

Here is a formula solution for your reference only

In A11 array ("Ctrl+Shift+Enter") formula copied right to D11, and all copied down :

=IFERROR(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,TRANSPOSE($A$3:$A$7)&"</b><b>"&TRANSPOSE($B$3:$B$7)&"</b><b>"&TRANSPOSE($C$3:$C$7)&"</b><b>"&TRANSPOSE($D$3:$I$7))&"</b></a>","//b["&(ROW($A1)*4+COLUMN(A$1))-4&"]"),"")

I think it will be easier to solve it by PQ in the "unpivot way", of which I would like to ask for @GraH - Guido , @AlanSidman or @p45cal to give you a help.

 

[contactshubhamyadav]

Thankyou very much @bosco_yip

I am trying the same formula but I dont know what mistake I am doing.

=IFERROR(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,TRANSPOSE($A$3:$A$5997)&"</b><b>"&TRANSPOSE($B$3:$B$5997)&"</b><b>"&TRANSPOSE($C$3:$C$5997)&"</b><b>"&TRANSPOSE($D$3:$J$5997))&"</b></a>","//b["&(ROW($A1)*5994+COLUMN(A$1))-5994&"]"),"")

 

[vletm]

contactshubhamyadav
Did You press Ctrl+Shift+Enter as asked?
Why do have there two times 5994? ... use four (4) as asked.

 

[AlanSidman]

Look at this video to learn how to unpivot your data

 

[p45cal]

Borrowing @bosco_yip 's file, PQ solution at cell F10 next to his solution for comparison.

 

[bosco_yip]

contactshubhamyadav
Did You press Ctrl+Shift+Enter as asked?
Why do have there two times 5994? ... use four (4) as asked.

Further to Vletm's explanation, here is some more information.

Extract the last part of the formula, it is always in :

=ROW($A1)*4+COLUMN(A$1)-4

Because it is suit with your designed 4 result columns

4 means a serial number will generate in starting from 1 & increase (1,2,3,4,5,6,7,8,9......) in a 4 columns x N rows formula cell areas, something like this :