Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 11:00 AM (PST)
Microsoft Tech Community
SOLVED

Filter function

Copper Contributor

hi

I have one table called metadata contains 3000+ rows of variables grouped in some classification.

 

I want to use Filter function to return all variables from Metadata (Name + Description) where the classification is matched against a list of classification strings (for example the one starting at K7) and return all variables matching those classification. The list at K7 is dynamic so I can't type individual string names because K7 list is the output of a different filter hence it's dynamic. Also this list is around 1 to 20 rows maximum. Meaning, I can't compare the classification of Metadata with the K7 classification list simply because the array size does not match (as suggested by different post where this could be possible if both arrays have equal size).

 

Could you please assist what to write in the Include field of Filter function to make it cross check the CLASSIFICATION of Metadata column 4 against the classification at K7?

 

Note that the propose formula above check one item as an example which is Weather. But again, I want to check against a variable list of strings of classification not fixed one.

 

thank you.

 

Untitled.png

6 Replies

@samery Not the most straight-forward formula, but it I believe it does what you ask for:

=DROP(
    IFERROR(
        REDUCE(
            "",
            K7#,
            LAMBDA(a, v,
                LET(
                    flt, FILTER(
                        Metadata[[Name]:[Description]],
                        Metadata[Classification] =
                            v,
                        ""
                    ),
                    return, VSTACK(
                        a,
                        HSTACK(
                            v,
                            flt
                        )
                    ),
                    return
                )
            )
        ),
        ""
    ),
    1
)

The attached file contains a working example.

@peiyezhu 
thanks for your response. I think the page you provided works if you know the target strings you are searching for. But in my case they are not known. Hence I won't be able to write individual strings in the index function. 

best response confirmed by Riny_van_Eekelen (Platinum Contributor)
Solution

@Riny_van_Eekelen That's quite the formula! :suprised: I like it, although a simple COUNTIF or ISNUMBER/XMATCH with the parameters reversed will suffice...

 

=FILTER(MetaData[[NAME]:[DESCRIPTION]], COUNTIF(K7#, MetaData[CLASSIFICATION 1]), "")
// OR:
=FILTER(MetaData[[NAME]:[DESCRIPTION]], ISNUMBER(XMATCH(MetaData[CLASSIFICATION 1], K7#)), "")

 

Cheers!

 

 

Thank you both, excellent contributions. Learned a lot from the nested formulas of Riny although I do enjoy your simplicity Djclements
Neat! I've been using MMULT to create criteria arrays but this solution is much simpler
1 best response

Accepted Solutions
best response confirmed by Riny_van_Eekelen (Platinum Contributor)
Solution

@Riny_van_Eekelen That's quite the formula! :suprised: I like it, although a simple COUNTIF or ISNUMBER/XMATCH with the parameters reversed will suffice...

 

=FILTER(MetaData[[NAME]:[DESCRIPTION]], COUNTIF(K7#, MetaData[CLASSIFICATION 1]), "")
// OR:
=FILTER(MetaData[[NAME]:[DESCRIPTION]], ISNUMBER(XMATCH(MetaData[CLASSIFICATION 1], K7#)), "")

 

Cheers!

 

 

View solution in original post