1

I currently have a response form where people are submitting requests for different items. Not every item is requested every time. The end goal is to automatically have them receive an email with a list of each item they requested after submitting the form.

Using =rangetoverticaltable from Form Mule, a Google Sheets add-on, you get a pretty table as below

The end goal is to get the same table returned but with the "water" column being filtered out as it is blank, i.e.:

I've read this post multiple times and I can't seem to figure out if the answer at the end of the post closes with it isn't possible or I'm missing a step.
I am currently using the formula
=arrayformula(if(not(isblank(A$2:A)),RANGETOVERTICALTABLE(FILTER($A$2:$E,A$2:A=A$2),$A$1:$E$1),""))
and this generates the first table as shown.

What can I add into the filter() section to remove the rows that have empty cells in column B?

The link to the google sheet is here.

Any ideas?

5
  • Welcome. Please try to use FILTER or QUERY (built-in functions) using rangetoverticaltable as the first argument of them.
    – Rubén
    May 1 '20 at 14:07
  • Thanks for the reply. I should have been more clear in where I am getting stuck. I am currently using the formula =arrayformula(if(not(isblank(A$2:A)),RANGETOVERTICALTABLE(FILTER($A$2:$E,A$2:A=A$2),$A$1:$E$1),"")) and this generates the first table as shown. What can I add into the filter() section to remove the rows that have empty cells in column B?
    – Medicgeek
    May 1 '20 at 16:16
  • Please edit the question to add the all the relevant details.
    – Rubén
    May 1 '20 at 16:19
  • Done, thanks for the advice.
    – Medicgeek
    May 1 '20 at 16:46
  • If all you're looking to do is add a condition to the FILTER statement, you could just add ,$B$2:$B<>"" just before the closing parentheses on FILTER()
    – kviLL
    May 3 '20 at 5:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.