Home > OS >  Convert matrix table into single row in Excel (no VBA)
Convert matrix table into single row in Excel (no VBA)

Time:04-21

In Excel, my Data looks like this

A  B  C  D

15 16 17 18
11 12 13 14
7  8  9  10

I am looking for a solution (without VBA) to transform my data into a single row, like this:

15 16 17 18 11 12 13 14 7 8 9 10

CodePudding user response:

For those who have access to the TOROW function:

=TOROW(A1:D3)

CodePudding user response:

Use this function:

=OFFSET(Matrix,MOD((COLUMN()-COLUMN($A$7)),ROWS(Matrix)),TRUNC((COLUMN()-COLUMN($A$7))/(ROWS(Matrix))),1,1)

CodePudding user response:

If you have TEXTJOIN() then try-

=TRANSPOSE(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,A1:D3)&"</s></t>","//s"))

enter image description here

  • Related