# How to test one cell of numerical data against many numerical filters?

I have 50-digit long binary IDs (whose numbers correspond to qualitative data of a two-consonant long string) that I want to filter based on partial matches to what I call filter IDs, which are also 50-digits long but in trinary. 0 = no, 1 = yes, 2 = do not evaluate. The idea is my custom STRINGMATCH function will output the number of matches between the data ID and filter ID. This number will be compared to the number of filters selected by the user. This method allows for unselected categories to be skipped in the evaluation, as well as the selection of data that matches all selected criteria, thus leaving untouched the data that meets only some of the selected criteria.

For example, let's look at just one:

• Data ID = 11000000000100001000000001000100000000000010000000
• Number of filters selected = 7 (not shown in picture)
• Filter ID = 12222222022122222222222221022202222222222212222222
• STRINGMATCH(Data ID, Filter ID) = 7
• Filter = TRUE

I can get this to work with one filter ID. But what if I want to run the same cell through arbitrarily many filter IDs, like say, a hundred? I mean, I could code a hundred IF-OR statements, but is there a way to more efficiently perform this task? Oh, if only Google Sheets had a FOR loop function...

Here is a test sheet.

Let me know if my question is too confusing or if there is no way to do this in Google Sheets. Thanks!

• Please edit your question and insert a table of sample data together with another table that shows your manually entered desired results. Also consider sharing a publicly editable sample spreadsheet. There is a blank sheet maker that lets you share safely. Commented Oct 1, 2023 at 6:17
• @doubleunary Here is the link. docs.google.com/spreadsheets/d/… Commented Oct 1, 2023 at 15:52
• I took a quick look and added an example of how to drop clusters. Please do not present new requirements after you have received an answer. Ask only one question per post. Post a new question instead. Commented Oct 1, 2023 at 21:54
• @doubleunary May I ask which "new requirements" I inadvertently added? I appreciate your help, by the way. Commented Oct 1, 2023 at 23:28

One easy way to match data like `11000000000100001000000001000100000000000010000000` with filters like `12222222022122222222222221022202222222222212222222 ` is to use a regular expression, like this:

``````=let(
data, A2,
filters, B2:B8,
map(filters, lambda(f, let(
regex, regexreplace(f, "2", "."),
if(len(f), regexmatch(data, regex), iferror(ø))
)))
)
``````

To learn the exact regular expression syntax used by Google Sheets, see RE2.

To screen data one filter at a time, use these patterns:

``````=arrayformula(
filter('Data IDs'!B2:B20, regexmatch('Data IDs'!A2:A20, left(regexreplace(B6, "2", "."), 25)))
&
torow(filter('Data IDs'!B2:B20, regexmatch('Data IDs'!A2:A20, right(regexreplace(B6, "2", "."), 25))))
)
``````
``````=arrayformula(
filter('Data IDs'!B2:B20, regexmatch('Data IDs'!A2:A20, left(regexreplace(B11, "2", "."), 25)))
&
torow(filter('Data IDs'!B2:B20, regexmatch('Data IDs'!A2:A20, right(regexreplace(B11, "2", "."), 25))))
)
``````

That gets you clusters that should be dropped. To drop all of those clusters from the data, use `map()` and the `isna(match())` pattern, like this:

``````=map(B19:T37, lambda(cluster, if(
isna(match(
cluster,
vstack(tocol(Y19:AQ37, 1), tocol(AV19:BN37, 1)),
0
)),
cluster,
iferror(ø)
)))
``````

That gets you a square matrix like the one you show as the two "manually filtered" tables. To get another result array shape, use `wraprows()` and `filter()`, like this:

``````=iferror(wraprows(
filter(
tocol(B19:T37),
isna(
match(
tocol(B19:T37),
vstack(tocol(Y19:AQ37, 1), tocol(AV19:BN37, 1)),
0
)
)
),
columns(B19:T37)
))
``````

See the 'test' sheet.