Forum Discussion
samery
Oct 27, 2023Copper Contributor
Filter function
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) wher...
- Oct 27, 2023
Riny_van_Eekelen That's quite the formula!
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!
Riny_van_Eekelen
Oct 27, 2023Platinum Contributor
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.
djclements
Oct 27, 2023Bronze Contributor
Riny_van_Eekelen That's quite the formula! 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!
- DaeyunPabloNov 24, 2023Brass ContributorNeat! I've been using MMULT to create criteria arrays but this solution is much simpler
- sameryOct 27, 2023Copper ContributorThank you both, excellent contributions. Learned a lot from the nested formulas of Riny although I do enjoy your simplicity Djclements