[SOLVED] Search multiple Excel sheets for values and exclude from list

Search multiple Excel sheets for values and exclude from list

I have a long list with items which needs to be distributed on my various sheets. I need the list to exclude the items from the list which have been distributed on any sheet. The items on the list is sorted on two criteria.

Below is my code snippet, which will be very long when I add all the sheets. Manual entering is not an issue - the length of the string seems to be :)

=FILTER(TableQuery[Item];(NOT(ISNUMBER(XMATCH(TableQuery[Item];Sheet1!$H$10:$H$50;0))))*(NOT(ISNUMBER(XMATCH(TableQuery[Item];Sheet2!$H$10:$H$50;0))))*(NOT(ISNUMBER(XMATCH(TableQuery[Item];Sheet3!$H$10:$H$50;0))))*((TableQuery[Criteria1]<>"")+(TableQuery[Criteria2]))))

As I tried to shorten the string, see below, it did't work.

=FILTER(TableQuery[Item];(NOT(ISNUMBER(XMATCH(TableQuery[Item];{Sheet1!$H$10:$H$50;Sheet2!$H$10:$H$50;Sheet3!$H$10:$H$50};0))))*((TableQuery[Criteria1]<>"")+(TableQuery[Criteria2]))))

Are there a smarter way to solve this issue? I might end up with more than 100 sheets. VBA is not interesting.

Solution

You could flatten a 3D referencing making use of TOCOL (and it's ability to exclude blanks): TOCOL(Sheet1:Sheets!H10:H50,1) (And make use of the error type of non matches using ISNA instead NOT(IS NUMBER())) Used in your logic: =FILTER(Table1[Item],ISNA(XMATCH(Table1[Item],TOCOL(Sheet1:Sheet3!H10:H50,1)))*((Table1[Criteria1]<>"")+(Table1[Criteria2])),"All items used")

With your separators: =FILTER(TableQuery[Item];ISNA(XMATCH(TableQuery[Item];TOCOL(Sheet1:Sheet3!H10:H50;1)))*((TableQuery[Criteria1]<>"")+(TableQuery[Criteria2]));"All items used")