Forum Discussion
blastlast
Feb 09, 2020Copper Contributor
Combining multiple ranges with filter-formula?
Hi! In Google Spreadsheets you can combine multiple ranges with the filter function with the use of { } as shown below. Is there any way of doing this with the new Filter-formula in Excel? Th...
PeterBartholomew1
Feb 10, 2020Silver Contributor
Like you, I am pretty sure the answer in no, it is not possible. What I am less convinced of is whether it should be possible? Firstly, I do not fully understand what the notation
= {range1, range2}
signifies. Something that might be useful, is to use the notation to define an array of ranges. At the moment this needs
= IF( {1,0}, range1, range2 )
That does not seem to be the case here, however. What you have described looks more like a sequence of FILTER operations, the results of which are to be appended. There must be more transparent notations for such a compound operation.
It is possible to produce what you describe in Excel with array methods but it is cumbersome. Filtering and appending lists is more PowerQuery than dynamic arrays. I also think Charles Williams's FastExcel addin features a function to append data to lists.
Kimuyam
Nov 16, 2022Copper Contributor
PeterBartholomew1 You can use VSTACK formula in excel 365 to combine
- PeterBartholomew1Nov 17, 2022Silver ContributorTrue, by the time one has VSTACK, HSTACK, CHOOSECOLS, TOCOL and WRAPCOLS, most things are possible, even if the notation is not as concise.