You should upgrade or use an alternative browser.

- Thread starter contactshubhamyadav
- Start date

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.

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

Last edited:

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

Did You press Ctrl+Shift+Enter as asked?

Why do have there two times 5994? ... use four (4) as asked.

Look at this video to learn how to unpivot your data

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

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

Did You press Ctrl+Shift+Enter as asked?

Why do have there two times 5994? ... use four (4) as asked.

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

=ROW($A1)*

Because it is suit with your designed