SOLVED

# element of a column equal any element of a list

Copper Contributor

# 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?

15 Replies

# Re: element of a column equal any element of a list

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)
)

# Re: element of a column equal any element of a list

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)

# Re: element of a column equal any element of a list

Something like that is part of the formula I provided.

= COUNTIFS(list, Table1[Field4]) > 0

returns the Boolean string you specified for [Field4].

# Re: element of a column equal any element of a list

I tried that but the formula returns an error:

The picture below is a screenshot of the formula + error message. The list if defined by the nested function FILTER(\$D\$5:\$S\$5;IF(ISBLANK(\$D\$6:\$S\$6);FALSE;TRUE);"0") and basically adds elements of line 5 to the list when the users put an x in line 6 below each element

# Re: element of a column equal any element of a list

(\$D\$5:\$S\$5 as the second argument for countif is just to check if it works. I would use the actual much longer dataset in real life)

# Re: element of a column equal any element of a list

Can't say I understood your question(s). As for the formula in the latest post, COUNTIFS() requires the reference, not the array returned by FILTER(). Close to your formula could be

=COUNTIFS(\$D\$5:\$S\$5, "", \$D\$6:\$S\$6, "<>") > 0

# Re: element of a column equal any element of a list

I guess an easier way to ask my question would be :

Let's say you have a column C of 98745 elements which can be filled with say 20 different text values.

Then you have a list containing any subset of these text values.

I need a way to seach through column C which returns 98745 booleans values; TRUE if the text value is contained in the list and FALSE if not

# Re: element of a column equal any element of a list

Key question is what do you mean under "I need a way to search", how result of the search are to be displayed - as separate list, or you'd like to apply filter to main column, or what.

# Re: element of a column equal any element of a list

The output of the function which take as entry Column C and my list in the format {"text value 1"\"text value 17"\"text value 8"\"text value 11"} needs to be {TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE; ... }

# Re: element of a column equal any element of a list

List {"text value 1"\"text value 17"\"text value 8"\"text value 11"} is defined as the range in the grid or by some other way, e.g. that's calculated array?

# Re: element of a column equal any element of a list

@SergeiBaklan

It is calculated.

Essentially the users of the spreadsheet tick options of what comes into the list so it will change and the formula needs to be able to recalculate as the options are ticked in/out.

Was that what you were asking ?

best response confirmed by pasdinspi (Copper Contributor)
Solution

# Re: element of a column equal any element of a list

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

# Re: element of a column equal any element of a list

@SergeiBaklan, Genius !

That worked, Thank you soo much

You are welcome

# Re: element of a column equal any element of a list

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) > 0

The workbook gives something of the feeling of going round in circles but there you go!

1 best response

Accepted Solutions
best response confirmed by pasdinspi (Copper Contributor)
Solution

# Re: element of a column equal any element of a list

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