Algorithmically assigning value to cell data in Google Sheets

I sometimes have to look through long sheets of data and pull out the most useful entries. Done manually, this is annoyingly repetitive because I am basically just looking for certain patterns. I decided there is likely a way to just program a way to prioritize these entires. The thought process went as follows:

  • Patterns that increase or decrease the value of an entry are finite and can be listed.
  • Pattern value can then be assigned
  • Cells can then be evaluated for the presence of all pattern data and be awarded a “score”
  • Scores below a certain number can be immediately discarded
  • Scored above can be manually inspected

The basic Google Sheets formula to do this is fairly simple:

=ArrayFormula(if(REGEXMATCH(D721, JOIN("|",{".ru",".eu",".net"})),-10,0))

This formula will look for domains and return a “-10” if they contain certain characters.

This can be combined with different evaluations in the creation of a score.