Using FILTER function and VSTACK function together

PeteSauces 

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!

PeteSauces 

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.

PeterBartholomew1 

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

PeteSauces 

Yes, it is virtually the same formula as the one we are already working on and it would address your problem, as would

Excel VSTACK :hamburger: - Quickly Combine Data from Multiple Sheets (Excel Noob Vs Pro) (youtube.com)

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!

Thanks SergeiBaklan.  If you see other communication gaps, please chip in!

PeteSauces 

If you look at the last formula I posted, you will see something that may look like code but is, in fact, simply a worksheet formula that may be placed anywhere on the sheet that you wish to see the result.  If you have defined the 3D range 'productLists' in Name Manager, you could simple type 

= TOCOL(productLists, 1)

or

= VSTACK(productLists)

into a cell and either should return your tables stacked down the sheet (see the 'Consolidated' worksheet).  As you need to apply further transformations to the data, traditional spreadsheet practice would be to wrap that bit of the formula within a further function, repeating the calculation as many times as you need to use it.

What the LET function allows you to do, is perform the first part of the calculation and assign the result to a variable, here called 'consolidatedList'.  This array is then used twice in the FILTER formula without needing to be first output to a helper range.  

PeteSauces 

If open the file PeterBartholomew1 shared in one of previous posts, all names are here

Ranges, no tables.