Forum Discussion
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?
This is the Google way (not working in Excel): =filter({G2:G20,P2:P20},{G2:G20,P2:P20}="Hello") which will show all the cells in the ranges G2:G20 and P2:P20 with "Hello".
I'm not in need of any workarounds, I'm just interested if this feature is possible with Filter in Excel?
Thanks!
- I don't use Google sheets so can you explain what you are trying to achieve and perhaps provide a sample spreadsheet?
Thanks for taking the time!
Here is a spreadsheet that shows the result I want. It's not just combining the two lists I'm after, I want to have a dynamic filter without any Index/match or Vlookup-workarounds.
https://docs.google.com/spreadsheets/d/1gbIy-06at_1PhTquwUSO5xfTah-PSB7-guC-XtoOWa0/edit?usp=sharing
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! 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!
