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.
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! This video 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!
