VSTACK and HSTACK functions.Merge multiple range of cells.(Microsoft Excel)
The VSTACK and HSTACK functions are new functions added to Excel in September 2022 to join multiple cell ranges.
The VSTACK function joins in the row direction and the HSTACK function joins in the column direction.
How it works
=VSTACK(array1 to N)
=HSTACK(array1 to N)
| Name | Omission | Explanation |
|---|---|---|
| array1 to N | Required argument. Specify the target cell range. |
Demonstrate
Specify two or more cell ranges as arguments.
Where the number of rows/columns do not match, a #N/A error will occur.
If you want to combine by specifying columns.
When creating such formulas, the data range is often indefinite, and you may want to specify columns.
However, specifying columns would result in an error #NUM! error because the number of rows exceeds Excel.
Register a formula to retrieve only valid rows in the Name Manager.
Click New.
Specify any function name in Name and the following formula in Reference range.
=LAMBDA(Area,LET(WorkArea,HSTACK(BYROW(Area,LAMBDA(Row,COUNTA(Row))),Area),MAP(DROP(FILTER(WorkArea,CHOOSECOLS(WorkArea,1)>0),,1),LAMBDA(Cell,IF(Cell="","",Cell)))))
It will be available with the specified function name. In the example, ValidLines.
=VSTACK(ValidLines(E:G),ValidLines(I:K),ValidLines(M:O))
If there are header rows, the DROP function removes the number of header rows.
=VSTACK(DROP(ValidLines(E:G),1),DROP(ValidLines(I:K),1),DROP(ValidLines(M:O),1))
Example of combining with other functions.
When combined with the SORT and SORTBY functions, multiple cell ranges can be merged and sorted.
=SORT(VSTACK(DROP(ValidLines(E:G),1),DROP(ValidLines(I:K),1),DROP(ValidLines(M:O),1)),2)
In the case of the SORTBY function, the sorting reference column is specified as a range of cells.
=LET(WorkArea,VSTACK(DROP(ValidLines(E:G),1),DROP(ValidLines(I:K),1),DROP(ValidLines(M:O),1)),SORTBY(WorkArea,CHOOSECOLS(WorkArea,2)))
Search from multiple cell ranges with XLOOKUP and VLOOKUP functions
=LET(WorkArea,VSTACK(DROP(ValidLines(E:G),1),DROP(ValidLines(I:K),1),DROP(ValidLines(M:O),1)),XLOOKUP("M00004",CHOOSECOLS(WorkArea,1),CHOOSECOLS(WorkArea,3)))
VLOOKUP and HLOOKUP functions can also be used.
Deduplicate multiple cell ranges with the UNIQUE function.
When combined with the UNIQUE function, multiple ranges can be deduplicated.
=UNIQUE(VSTACK(DROP(ValidLines(E:G),1),DROP(ValidLines(I:K),1),DROP(ValidLines(M:O),1)))
FILETER function to search through multiple cell ranges
The FILTER function can be used to search through multiple cell ranges.
=LET(WorkArea,VSTACK(DROP(ValidLines(E:G),1),DROP(ValidLines(I:K),1),DROP(ValidLines(M:O),1)),FILTER(WorkArea,CHOOSECOLS(WorkArea,3)>=300))
