Forum Discussion
Combining multiple ranges with filter-formula?
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.
PeterBartholomew1 You can use VSTACK formula in excel 365 to combine
- True, by the time one has VSTACK, HSTACK, CHOOSECOLS, TOCOL and WRAPCOLS, most things are possible, even if the notation is not as concise.
I'm not sure if I understand what you mean? In Google Spreadsheets it is possible and it is a great feature to be able to make advanced filters of multiple ranges or sheets.
But as I understand, it's not possible in Excel, only Google Sheet.
Thanks!
blastlast While the method is not the same as Google Sheets, the result is possible in Excel! https://youtu.be/Onudkw9DMlU?t=240 best explains but essentially you'll want to have an original filter function with the full range then wrap the original filter function with a new filter function that has an {array} for the "include" parameter with 1s for the number column(s) you do want and 0s for the number column(s) you do not want.
For example, say I wanted to a range (A to D) where D = "Microsoft" but only wanted to select columns A and C, I'd insert in the formula
=FILTER(FILTER(A2:D10,D2:D10="Microsoft"),{1,0,1,0})
Hope this helps!
