I have an array formula that looks like this:

=ArrayFormula(IF(COUNTIF(B2:B, QUERY('Form Responses'!B2:G, "select G where B != ''"))=0, IFERROR(QUERY('Form Responses'!B2:G, "select B, G where B != ''"), "")))

It retrieves data from a different sheet that's connected to Google Forms. With the first IF and COUNTIF I try to check whether the G column from the form response sheet is already present in the B column in my current sheet. If that is not the case, I query column B and G.

However, if the value is present already, I don't want to query it, but I also don't want an empty line, which is now happening because of the "" as the third argument for the IF statement.

Is there a way in which I can only query if not a duplicate, without adding an empty line otherwise?

  • share a copy of your sheet – user0 Jul 3 at 12:26
  • the "" is part of IFERROR not IF therefore try:

     IFERROR(QUERY('Form Responses'!B2:G, "select G   where B !=''", 0)))=0, 
     IFERROR(QUERY('Form Responses'!B2:G, "select B,G where B !=''", 0)), )), 
     "where Col1 is not null", 0)

  • Thank you for catching that! However, it still results in empty lines when the IF statement is false. – Niels Kersic Jul 3 at 20:08
  • and you want what exactly? to remove empty lines in between and create a compact of results?? – user0 Jul 3 at 20:12
  • Yes. When the IF statement is false, it should go to the next entry in the query instead of returning an empty line. – Niels Kersic Jul 3 at 20:24
  • @NielsKersic answer updated, try now – user0 Jul 3 at 20:31
  • 1
    That did the trick! Thanks – Niels Kersic Jul 4 at 9:03

Your Answer

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

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