This Page: cpearson.com/Excel/VectorToMatrix.aspx

Last Updated: 06-May-2018

Copyright 1997 - 2014, Charles H. Pearson

Site Last Updated: 21-Jun-2020

Advanced Software

Design And Development

Design And Development

Office Integration Projects
NET Programming
XML Development

Search The Site:

Transforming Row And Column Ranges To A Matrix

This page describes formulas for converting a row or column to a matrix.

You may have data in a row or column that you want to transform into a matrix. This page describes formulas you can use to accomplish that. First, we'll look at transforming a column to a matrix. Next, we'll look at transforming a row to a matrix.

Column To Matrix

Suppose you have a single column of data in a range named *TheCol2*. This is a single column spanning several rows. To convert
it to a 2xN matrix, two rows and N columns where N = (rows of *TheCol2* divided by 2), you can use the formula below. This formula
is entered cell D9. Change the reference to
$D$9 to the first cell in which you
enter this formula.

=OFFSET(TheCol2,COLUMN()-COLUMN($D$9)+((ROW()-ROW($D$9))*(ROWS(TheCol2)/2)),0,1,1)

Copy this formula across the row into *N* cells where *N* is one half the number of columns of TheCol2. Then,
copy these cells down one row. For example, if TheCol2 is B8:B16 and you enter the formula
initially in cell D9, you would copy the formula across to G9 and then copy D9:G9
down one row to fill D9:G10. If you want more rows and fewer columns, change ROWS(TheCol2)/2 to
ROWS(TheCol2)/N where N is the number of rows to fill. A screen shot of the data TheCol2
and the result matrices are shown below:

Row To Matrix

A similar operation can be performed when the source data is in a single row. Suppose you have a single row of data in the range B22:I22, with
a defined name of *TheRow*. To convert this to a matrix, enter the following formula in cell E25:

=OFFSET(TheRow,0,COLUMN()-COLUMN($E$25)+(ROW()-ROW($E$25))*(COLUMNS(TheRow)/2),1,1)

Change the reference in the formula to $E$25 to the first cell in which you enter this formula. Copy this formula across to
H25, or half the number of cells in the range *TheRow*. Then, copy those cells down one row. The formula should now
be entered into the range E25:H26. To span more rows and fewer columns, change COLUMNS(TheRow)/2 to
COLUMNS(TheRow)/N where N is the number of rows in the resulting matrix. A screen shot of the data and resulting matrices is
shown below:

For formulas that do the reverse of these functions, namely create a vector (row or column) from a matrix, see the Matrix To Vector page.

You can download the file with all the example formulas on this page. |

This page last updated: 31-January-2011. |