Forum Discussion
element of a column equal any element of a list
Hello, I am using a multi-parameter filter function and for one of the "include" parameter, I want to select any column that contain one of any elements of a pre-defined list. The problem is, the list changes dynamically when users select some elements, so I need a way to go through a column of say 78945 elements and return 78945 TRUE or FALSE values, them being TRUE if the content of the cell is present in the list and FALSE if not.
I haven't found any way to do this, is there a function I might have overlooked?
Thanks a lot in advance
Not exactly. I'm not sure on what the user ticks and what is generated as result. The key is generated list is the reference, i.e. part of the grid, or an array generated by the formulae. If the latest that could be like
=LET(source, TOCOL(C:C,3), targets, {"c111";"c151"}, NOT( ISNA(XMATCH( source, targets))) )for the
15 Replies
I might as well include where I had go to covering as many bases as I could think of!
Adding new items to the table on the left adds to the options table using the formula
= SORT(UNIQUE(FILTER(list, list<>"")))Conditional formatting exposes the check boxes. The use can then select to subset of the options as being of relevance. A filtered selection is achieved using
= FILTER(options, checkBox)Conditional formatting is also used for the yellow highlighting.
where 'options' is a dynamic range and 'checkBox' is on offset range based upon 'options'. The Boolean array searches the original 'list' for values that appear in the selection
= COUNTIFS(select, list) > 0The workbook gives something of the feeling of going round in circles but there you go!
Not the most inspiring of use cases, but is this the kind of thing you are trying to do?
= LET( criterion, BYCOL(Table1, LAMBDA(field, OR(COUNTIFS(list, field)))), FILTER(Table1[#All], criterion, TRUE) )- Kind of - but somewhat simpler -
If my list was {"horse","cow"}, I would like to apply it to field4 and have it display 11 FALSE values then TRUE FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE no matter if you filled field4 with a bunch of other non-horse and cow values (I hope this made sense)Something like that is part of the formula I provided.
= COUNTIFS(list, Table1[Field4]) > 0returns the Boolean string you specified for [Field4].
