Forum Discussion
Using FILTER function and VSTACK function together
We haven't got to VSTACK yet, never mind REDUCE, so I guess there is further to travel!
Combined#
= TOCOL(productLists, 1)
"Product list"
= FILTER(combined#, ISNUMBER(SEARCH(selectedCompany, combined#)))
PeterBartholomew1 omg, it is working so well. thank you for that 3D range stuff, which is covering all tabs between first:last! mentioned, whoa 🙂
(once we had a short chat discussion but do not remember when and what about, only that it was Excel)
as a "gift" in return added two twists to your solution:
--in column B added a monitoring formula to highlight if a product name contains >1 brand name
--replaced your data validation dropdown in G1 to a fancy(!?) quick 2+1 click selection method, hope you will like it
Thanks for the suggestions! The first:last! strategy is good in that it ensures added sheets are included in the 3D range. I hadn't come across the INDIRECT/CELL strategy, nor the double-click to force a recalc (F9 also works but perhaps lacks the elegance).
I worked through your formula to see what it was doing and came up with an alternative test to check that the user has clicked a company from the list.
= LET( ref, INDIRECT(CELL("address")), selected, IF( ISREF(ref distinctCompanies), ref, "doubleclick any cell then single click on a company" ), selected )- PeterBartholomew1
funny is that i was trying that space intersection method when created my twists but was not working however one of my favourites of "old" Excel tricks. maybe i have improperly set up my named ranges. so promoting your alternative to master then, thanks 🙂
- But there are 10-15 more sheets in my Excel workbook, so I am wondering how to filter without combining the sheets..
I have used a 3D range to group the various data tables. That requires the tables to be identically positioned on each sheet. Other options are available but require lists of source data to be maintained.
= LET( consolidatedList, TOCOL(productLists, 1), FILTER( consolidatedList, ISNUMBER(SEARCH(selectedCompany, consolidatedList)) ) ) The defined name "productLists" refers to the 3D range =Products:Clearance!$A$2:$A$21- The reason I wanted to not consolidate the data into one sheet is because this is not the actual workbook... The actual workbook has 10-15 sheets and a few sheets have about 65 lines of unique data, some sheets have 40 lines of unique data, some have only 15 lines of unique data...
Also, I would like to protect these worksheets, so none of the information in them is changed..
Peter
https://techcommunity.microsoft.com/t5/excel/macro-help/m-p/3976333#M210843
Why not combine all sheets to one and then filter?//select * from consolidateSheet limit 20;
select f03 from consolidateSheet where regexp("3M",f03)
