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

Question

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!

Answer 1

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

See let(), map(), lambda(), regexreplace() and regexmatch().

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.