1

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?

Improve this question
1
  • share a copy of your sheet
    – user0
    Commented Jul 3, 2019 at 12:26

1 Answer 1

Reset to default
1

  • the "" is part of IFERROR not IF therefore try:

    =QUERY(ARRAYFORMULA(IF(COUNTIF(B2:B, 
 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)

Improve this answer
5
  • Thank you for catching that! However, it still results in empty lines when the IF statement is false.
    – Niels Kersic
    Commented Jul 3, 2019 at 20:08
  • and you want what exactly? to remove empty lines in between and create a compact of results??
    – user0
    Commented Jul 3, 2019 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
    Commented Jul 3, 2019 at 20:24
  • @NielsKersic answer updated, try now
    – user0
    Commented Jul 3, 2019 at 20:31
  • 1
    That did the trick! Thanks
    – Niels Kersic
    Commented Jul 4, 2019 at 9:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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