0

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.

The control panel for the filters

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!

4
  • 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/…
    – Ylahris
    Commented Oct 1, 2023 at 15:52
  • 1
    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.
    – Ylahris
    Commented Oct 1, 2023 at 23:28

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.