In Google Sheets I am trying to put columns of text together into one cell, ignoring blank cells, with If statements

userlpWl1 0
Viewed 34

To begin, the spreadsheet is here.

I have "Sheet1" that has columns A-I titled with movie genres and descriptors below. On "Sheet2" I have each genre next to a checkbox. The end goal is to be able to select any and all of the genres that apply and have the descriptors put together into one cell (Sheet2!A5) separated by a comma. I want to include the entire column of each genre ignoring the blank cells so I can add and take away descriptors as needed without breaking anything.

Below is the expected result if all are selected enter image description here

I am very new to the world of functions and have spent hours at this point trying to browse all the forums and various how to videos, but I haven't been able to find anything close enough to work.

Below is what I tried. enter image description here

I have updated the permissions for anyone with the link to edit. Here is the link once more. Any help is GREATLY appreciated. Thank you in advance for your time and efforts. I know how important those are. : )

textjoin checkbox function google-sheets
1 Answers

I went ahead and edited your spreadsheet for you, but here is the core formula that is used:

=ARRAYFORMULA(
  TEXTJOIN(
    ", ", 
    TRUE, 
    QUERY(
      Sheet1!A2:I, "
      SELECT 
        "&TEXTJOIN(
          ", ", 
          TRUE, 
          FILTER(
            REGEXEXTRACT(
              ADDRESS(1, MATCH(Sheet2!A:A, Sheet1!1:1, 0)), 
              "[A-Z]+"
              ), 
              Sheet2!B:B=TRUE
            )
          )
      , 0
      )
    )
  )

Essentially, you need to figure out which columns (i.e. genres) on Sheet1 to include. Then, you pass those column letters to a SQL string via QUERY(), which returns the curated set of movie titles. Finally, you TEXTJOIN those titles together.

Hope it helps.

userTMKw0
Related