• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to transpose in excel ?

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

79233
 

bosco_yip

Excel Ninja
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.


79234
 

Attachments

Last edited:
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&"]"),"")
 

bosco_yip

Excel Ninja
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 :

79245
 
Top