Forum Discussion
Using FILTER function and VSTACK function together
Hi,
I am trying to use FILTER function and VSTACK function together to find out how many products that different companies sell and list them by the full text...
Please help!!
Peter
29 Replies
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 )
- 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
- ارجو وجود تØديث تلقائي للبيانات
- Does anyone know how to help me???
I looking to filter data across multiple sheets...
I using the names of various grocery-store products because I want to remain anonymous about the work I do and who I am...
It all depends on the nature of your input data. It may amount to no more than stacking the input tables and selecting unique combinations for display in the appropriate format.
- What problem are you facing to use these functions? Try like =VSTACK(FILTER(ProductList,CompanyName=Criteria1),FILTER(ProductList,CompanyName=Criteria2)). Attach a sample file and then show your desired output. Otherwise we can't understand your problem.
- How do I attach a file?
If you don't see this option
put the file on OneDrive or like resource, share with everyone and post the link here.
