Suppose I have the following matrix:123456789101112 And I want to convert this matrix into a column vector, such that the order of elements allocated into the column vector follows the order of elements of the first row, then moving to the next rows. Something that would give the following output:123456789101112 The closest code that I got to execute this was:=INDEX($A$1:$C$4,INT((ROW()-ROW($D$1))/COLUMNS($A$1:$C$4))+1,MOD(ROW()-ROW($D$1),COLUMNS($A$1:$C$4))+1) But what I don't like about this code is that to have the output I have to manually drag the mouse. Thank you for your time and attention!

lsantan3 If you are on Microsoft-365 then could use TOCOL() function.=TOCOL(A1:C4)For Excel-2019, 2021 could utilize FILTERXML() with TEXTJOIN(). =INDEX(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,$A$1:$C$4)&"</s></t>","//s"),ROW(1:1))

lsantan3

Copper Contributor

Apr 12, 2023

Solved

How to convert a generic matrix into a column vector?

Suppose I have the following matrix:

1	2	3
4	5	6
7	8	9
10	11	12

And I want to convert this matrix into a column vector, such that the order of elements allocated into the column vector follows the order of elements of the first row, then moving to the next rows. Something that would give the following output:

1
2
3
4
5
6
7
8
9
10
11
12

The closest code that I got to execute this was:

=INDEX($A$1:$C$4,INT((ROW()-ROW($D$1))/COLUMNS($A$1:$C$4))+1,MOD(ROW()-ROW($D$1),COLUMNS($A$1:$C$4))+1)

But what I don't like about this code is that to have the output I have to manually drag the mouse.

Thank you for your time and attention!

excel

Formulas and Functions

Harun24HR
Apr 13, 2023
lsantan3 If you are on Microsoft-365 then could use TOCOL() function.
=TOCOL(A1:C4)
For Excel-2019, 2021 could utilize FILTERXML() with TEXTJOIN().
=INDEX(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,$A$1:$C$4)&"</s></t>","//s"),ROW(1:1))

Forum Discussion

How to convert a generic matrix into a column vector?

3 Replies

Resources