Forum Discussion

Copper Contributor

Sep 23, 2023

Solved

Filter function with a range as a criterium.

Hi, I need help with Excel, trying to filter out a range with values and text, by another range criterium with text. I am using a filter function but it can only use one criteria, not a range of cr...

excel

Formulas and Functions

SergeiBaklan

Sep 23, 2023

emfilipovski

For such sample

it could be

Meet:

=VSTACK(
    Table1[#Headers],
    FILTER( Table1,
        MMULT(--(Table1[Name]=TRANSPOSE(Criteria[Criteria])),
              SEQUENCE(ROWS(Criteria[Criteria]),,1,0)
        ) ) )

Don't meet:

=VSTACK(
    Table1[#Headers],
    FILTER( Table1,
        MMULT(
            --(Table1[Name]=TRANSPOSE(Criteria[Criteria])),
            SEQUENCE(ROWS(Criteria[Criteria]),,1,0)
        ) -1 ) )

filter table.xlsx16 KB

emfilipovski

Copper Contributor

Sep 23, 2023

And I also need the other values in a separate formula, that are not in the Criteria list. The remaining values are Apple, Orange, KKK....

SergeiBaklan

MVP

Sep 23, 2023

emfilipovski

Please clarify "111 Apple" is one value in one cell, or that's 111 and Apple in 2 cells.

emfilipovski
Copper Contributor
Sep 23, 2023
2cells, 111 is in one cell and Apple is in another cell.
- SergeiBaklan
  MVP
  Sep 23, 2023
  emfilipovski
  
  For such sample
  
  it could be
  
  Meet:
  
  =VSTACK( Table1[#Headers], FILTER( Table1, MMULT(--(Table1[Name]=TRANSPOSE(Criteria[Criteria])), SEQUENCE(ROWS(Criteria[Criteria]),,1,0) ) ) )
  
  Don't meet:
  
  =VSTACK( Table1[#Headers], FILTER( Table1, MMULT( --(Table1[Name]=TRANSPOSE(Criteria[Criteria])), SEQUENCE(ROWS(Criteria[Criteria]),,1,0) ) -1 ) )
  filter table.xlsx16 KB
  - emfilipovski
    Copper Contributor
    Sep 24, 2023
    Thank you, this seems to work. Hopefull no errors can occur because it is intended to be dynamic and change.... this is the formula I used =FILTER($AI$6:$AJ$601;
    MMULT(--($AJ$6:$AJ$601=TRANSPOSE($AK$4:$AK$599));
    SEQUENCE(ROWS($AK$4:$AK$599);;1;0)
    ))