Arrays To Columns
This page has been replaced by the Matrix To Vector page.
Often, it is useful to create a single column or row of data from a MxN range of data. For example, when charting you cannot assign a rectangular range to a single data series. The series must be in a single column or row of data. This problem came up with a user who had an array of measurement values, with 31 columns (day of month) and 24 rows (hour of day). He wanted to create an XY-Scatter chart, displaying the measurement values. Since the y-axis data (measurement values) had to be a single column of data, we had to convert the array to a column. The following formulas assume that the data is in a range named "MyRange" with M rows and N columns.
|
||
To create a column of data containing your original data in row-by-row format, use the following formula. Row-by-row means that the first N entries contain the first row of "MyRange", the next N entries contain the second row of "MyRange", and so on. Name the range that will contain the data "CxRV". =INDIRECT(ADDRESS(
ROW(MyRange)+INT((ROW()-ROW(CxRV))/COLUMNS(MyRange)), Enter this formula in the first cell of the range CxRV, and use Fill Down to fill the entire range.
|
|
|
To create a column of data containing your original data in column-by-column format, use the following formula. Column-by-column means that the first M entries contain the the first column of "MyRange", the next M entries contain the second column of "MyRange", and so on. Name the range that will contain the data "CxCV". =INDIRECT(ADDRESS(ROW(MyRange)+MOD(ROW()-ROW(CxCV),ROWS(MyRange)), Enter this formula in the first cell of the range CxCV, and use Fill Down to fill the entire range.
|
|
|
To create a row of data containing your original data in row-by-row format, use the following formula. Row-by-row means that the first N entries contain the first row of "MyRange", the next N entries contain the second row of "MyRange", and so on. Name the range that will contain the data "RxRV". =INDIRECT(ADDRESS(ROW(MyRange)+INT((COLUMN()-COLUMN(RxRV))/ Enter this formula in the first cell of the range RxRV, and use Fill Right to fill the entire range.
|
||
To create a row of data containing your original data in column-by-column format, use the following formula. Column-by-column means that the first M entries contain the the first column of "MyRange", the next M entries contain the second column of "MyRange", and so on. Name the range that will contain the data "RxCV". =INDIRECT(ADDRESS(ROW(MyRange)+MOD(COLUMN()-COLUMN(RxCV),ROWS(MyRange)), Enter this formula in the first cell of the range RxCV, and use Fill Right to fill the entire range.
|
||
In all of the formulas listed, be sure to change "Sheet1" to the name of worksheet containing MyRange. Note that if a cell is in MyRange is blank, it will
appear as a zero in a cell containing these formulas. This is a result of the =INDIRECT function. You can get around this by using an =IF statement: Since these formulas are long, you may find it useful to assign the =INDIRECT(...) formula to a defined name, and then enter either These formulas have the advantage over a macro-based solution because whenever data is altered in MyRange, the changes are automatically reflected in the column or row range, without further user intervention. Download a worksheet illustrating these formulas.
|