TransposeArray
The TransposeArray() function returns the transpose of a range (vertical columns are returned as horizontal rows or vice versa).
Related functions: ArrayToString, StringToArray, SubstituteArray
Syntax
TransposeArray (Range)
| Range | The range of cells in Excel used as input |
Note:
Input parameters can consist of Quintessence functions. This includes functions that return ranges in cases where a parameter value can be a range.
Why use TransposeArray() as opposed to Excel’s Transpose() function?
Transforming a column to a row (or vice versa) can be achieved in various ways. The desired result in this example is to transform the input column:
| A |
| B |
| C |
into an output row:
| A | B | C |
Consider the following four formulas:
| Input array | Formula used | Four sets of output rows | Comment | |||
| A | =Transpose(A1:A3) | A |
The Excel Tranpose function does not recognise the input range as an array. It therefore only transposes the first value.
|
|||
| B | {=Transpose(A1:A3)} | A | B | C |
To fix the issue above, highlight the three output cells, type in the function, and press CTRL+SHIFT+ENTER. This instructs Excel to treat the input range as an array. This is indicated by the presence of the brackets {} and produces the correct result.
|
|
| C | =Fx(Transpose(A1:A3)) | A | B | C |
Another way to achieve the same result is to use the Quintessence Fx() function. This instructs Excel that the input range is an array, and produces the correct result.
|
|
| =TransposeArray(A1:A3) | A | B | C |
TransposeArray produces the correct result within a single function. There is no requirement to press CTRL+SHIFT+ENTER or to place the function inside the Fx() function. The input range is always treated as an array.
TranposeArray has been named to differentiate it from Excel’s Tranpose function and also to indicate that it treats input ranges as arrays.
|
||
Examples
Example 1. Transposing a single column
Example 2. Transposing multiple columns
Example 1. Transposing a single column
The input range consists of a list of bond codes. The TransposeArray() function outputs the column of bond codes as a row.
| Range | Transpose |
| Asset Code | =TransposeArray(A2:A6) |
| JWQ | |
| TVJ | |
| VWE | |
| GZW |
| Range | Output | ||||
| Asset Code | Asset Code | JWQ | TVJ | VWE | GZW |
| JWQ | |||||
| TVJ | |||||
| VWE | |||||
| GZW | |||||
Example 2. Transposing multiple columns
The input range consists of a list of bond codes, associated dates and prices. The TransposeArray() function is used to output the input columns as rows.
| Range | Transpose | |||
| Asset Code | Date | Price | =TransposeArray(A2:C6) | |
| JWQ | 2015-02-28 | 125.23 | ||
| TVJ | 2015-02-29 | 110.56 | ||
| VWE | 2015-02-30 | 102.68 | ||
| GZW | 2015-02-31 | 94.57 | ||
| Range | Output | |||||||
| Asset Code | Date | Price | Asset Code | JWQ | TVJ | VWE | GZW | |
| JWQ | 2015-02-28 | 125.23 | Date | 2015-02-28 | 2015-02-29 | 2015-02-30 | 2015-02-31 | |
| TVJ | 2015-02-29 | 110.56 | Price | 125.23 | 110.56 | 102.68 | 94.57 | |
| VWE | 2015-02-30 | 102.68 | ||||||
| GZW | 2015-02-31 | 94.57 | ||||||