Optimizing code for Name database in Google Sheets

Question

I looking for help to optimize the code for a Name database I created in Google Sheets. I'm not a programmer and I have made this by researching on Youtube, Stack Overflow and other sites.

The data sheets now work as intended, but the code is way too long and it takes time for the sheet to load up and gets laggy sometimes. The database is basically a tag filter system where is excludes any name that doesn't include all the tags selected.

This is the link to the Google Sheet:

https://docs.google.com/spreadsheets/d/1g1GjgVzABXHDOsxCQS3x9F3lgeIjyxnZggbDcZj04AM/edit?usp=sharing

The code I need to optimize is in the "Search" tab in W3, X3, Y3, Z3, AA3. Thanks!

It's been a while since I have seen the code so I don't remember well. There was a function(s) I tried that would compact all the cells from a selected range, instead of writing each cell like in my code, but it did not work.

doubleunary · Accepted Answer · 2023-01-04 18:09:46Z

2

Use filter() and regexmatch(), like this:

=lambda( 
  regex, 
  iferror( 
    filter( 
      'Master Data'!A3:F, 
      regexmatch( 
        'Master Data'!AX3:AX, 
        regex 
      ) 
    ), 
    "(no matching data)" 
  ) 
)( 
  textjoin( 
    "\b.*?\b", true, 
    sort( 
      flatten( 
        bycol( 
          sequence(1, columns(H2:U2) / 2, 0, 2), 
          lambda( 
            colOffset, 
            iferror( 
              filter( 
                offset(I3:I, 0, colOffset), 
                offset(H3:H, 0, colOffset) 
              ) 
            ) 
          ) 
        ) 
      ) 
    ) 
  ) 
)

Put the formula in cell Search Tab!W3. It will fill columns W:AB automatically.

answered Jan 4, 2023 at 18:09

doubleunary

22k13 gold badges38 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Optimizing code for Name database in Google Sheets

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related