Forum Discussion
Using FILTER function and VSTACK function together
I am trying to use exactly the same type of formula used in this YouTube video, but it is not working.... So I am wondering how to use a similar strategy...
https://www.youtube.com/watch?v=Au-j0ytEqF4
Please let me know.
Peter
Yes, it is virtually the same formula as the one we are already working on and it would address your problem, as would
which includes a bit of amateur theatricals!
I use named references for everything (including the 3D reference) but that is because my strategy is "if I know what a value represents, I name it to provide meaning; if I don't know what a value represents, I delete it to avoid misuse!" Not everyone would get on with that as a strategy!
- Great!! Now, how do I copy and paste the results of that formula for each "company" into a seperate workbook??
The final formula is pretty much as before. If though, we switch to using VSTACK because it applies to entire tables and that is the function used in the videos, we get
= LET( consolidatedList, VSTACK(productLists), FILTER( consolidatedList, ISNUMBER(SEARCH(selectedCompany, consolidatedList)) ) )The name 'consolidatedList' is used for a partial calculation within the formula but does not appear anywhere on the worksheet and only exists in memory while the LET function is being evaluate [i.e. the LET function defines the scope of the name; the name simply does not exist outside that scope].
If you are thinking "WTF, this looks nothing like the spreadsheets I was brought up on!", I can only agree. For me that is something of a relief; you might find it somewhat more challenging!
- PeterBartholomew1
So what would be the final formula? Yes, it is possible. In fact that is what the formulas do. I show a consolidated list, but that is only for information. If it is deleted the filtered lists still work.
BTW. Separate tests to determine whether the row is blank and whether the company matches the selection are unnecessary. A blank row automatically fails the test so does not appear in the filtered result.
Ok. So I do not want to consolidate all of the data into another sheet.. instead I would like filter all of the data in the seperate sheets without consolidating them, and show the results of what has been filtered..Please let me know if this is possible.
Peter