|
HDR.VSTACK Function
Matches cells in the first header row of the array/range to header row cells from multiple arrays or ranges to create an output array with a single header row. Up to 29 Range /Array arguments can be appended below one another row wise.
Each column with a matching header is stacked below it’s matching column.
Columns in subsequent arrays/ranges where column headers do not match the first array/range column header are filled with Fill characters, for example #N/A (default) or 0.0 or "".
HDR.VSTACK is a multi-threaded, non-volatile array function.
VSTACK Syntax
HDR.VSTACK ([Fill], Arg1, Arg2, … , Arg29)
Fill (Optional, default #N/A)
A cell or character or digit to be used to fill unused cells in the output array, for example 0.0 or “”.
When the Arrays or ranges to be appended do not have matching column headers the gaps will be filled with the Fill character. Use zero if you want to append differently-sized numeric arrays or arrays with some different header cells and pass the output to an aggregating function such as SUM() or a single-cell array formula.
Note: A non-array argument given for the first argument to HDR.VSTACK will be treated as a fill value. Preface the non-array argument with a comma to make it vthe first HDR.VSTACK item.
Arg1 to Arg28
Ranges or arrays to be appended.
Where cell values in the first row of each Range/Array match the cell values in the first row of the first range/array the columns are placed in the output array in that matched column. The sequence of columns can be different in each array/range.
Only the first row (header row) in the first range/array is copied into the output array. The header rows in subsequent arrays/ranges are not copied.
The ranges to be appended can be on different sheets.
If an Argument range/array is a constant or resolves to a single value it will be propagated across the entire row.
Stacking and Slicing Examples
The first formula =HDR.VSTACK(A4:B9,A12:B17) stacks the 2 ranges on top of one another, but re-arranges the columns from the second range to match the columns from the first range by using the column headers.
The second formula takes slices out of the array returned by the HDR.VSTACK but uses an array of relative row numbers working upwards from the bottom of the array {-1,-2,-3,-4,-5}
.png)
|
