Function Reference: TOCOL, TOROWS
| Syntax | ||
| =TOCOL(array,[ignore],[scan_by_column]) =TOROW(array,[ignore],[scan_by_column]) |
||
| Purpose / Description | ||
| Returns an array in which the values from the initial array have been transferred to a single column or single row. It will optionally skip errors or blanks, and the direction of scanning is controlled by the final optional argument.. | ||
| Arguments | ||
| Name | Type | Description |
| array | reference or array of any | A reference or nested expression that returns a valid reference, a constant array of any values or a nested expression that returns an array of any values. Accepts references to multi-sheet ranges. |
| [ignore] | number | A reference to a range, a nested expression or constant number that determines whether errors or blanks are skipped. The number entered must be 0 ≤ n < 4. If the value is not an integer, it is truncated to the next lowest whole number. Logical and string values are coerced to number value. If the value is 0 or omitted, neither blanks nor errors are skipped in the output array. If the value is 1, blanks are skipped and not included in the output array. Note that empty strings ("") are not blanks, so they will be returned. If the value is 2, errors are skipped and not included in the output array. If the value is 3, then both blanks and errors are skipped and not included in the output array. |
| [scan_by_ column] |
logical | If the value entered is a string or number, the standard coercion rules apply and the value is coerced to a logical. If the value is FALSE or omitted, array is scanned by row and the entries from the first row appear above (TOCOL) or to the left (TOROW) of the next row's values. If the value is TRUE then array is scanned by column and the entries from the first column appear above (TOCOL) or to the left (TOROW) of the next column's values. |
| Return Values | ||
| Type | Specific Value | Description |
| array of any | Regardless of whether the original array is a reference to a range, a calculated array, or a constant array, the result returned is an array containing the values from the array, arranged as a single column or single row of values, omitting blanks or errors based on the value of ignore. | |
| error | #VALUE! | If the value of ignore argument is not in the range: 0 ≤ n < 4. If the values passed to ignore or scan_by_column arguments cannot be coerced to the correct number or logical value type respectively. |
| error | #NUM! | If the size of array is too large. |
| error | #SPILL! | If the number of cells required is more than the remaining cells in the column (TOCOL) or more than the remaining cells in the row (TOROW), in addition to the usual issue with obstructive cell contents. |
| error | Any errors referenced by arguments are returned due to propagation of error values, except that if the value of ignore is 2 ≥ n < 4 then any errors in the array argument will be skipped. But errors in the ignore and scan_by_column arguments will be propagated.. | |
| Notes / Comments / Advice / Warnings | ||
| Comment: | These are useful additions to Excel, allowing for the reformatting of table and data table data, into columns and rows for reporting. | |
| Comment: | The fact that the array argument accepts multi-sheet references means that these functions can be used to collate data from multiple sheets to summary and dashboard worksheets. | |
| Comment: | To skip empty strings in the array argument, wrap the reference to array in an IF function, such as IF(TRIM(range),NA(),range) and use 3 for the value of the ignore argument. This will convert any cells containing empty strings to errors which will then be skipped, due to the value of ignore. | |
| Examples: | Open / download sample workbook: TOCOL, TOROW, WRAPCOLS, WRAPROWS.xlsx | |