Google Sheets – Skip ARRAYFORMULA If Result Is Blank

formulasgoogle sheetsgoogle-formsgoogle-sheets-arrayformulagoogle-sheets-query

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?

Best Answer

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)

Formula

=ARRAYFORMULA(array_expression)
=VLOOKUP(search_criterion, array, index, sort_order)
=IF(test, then_value, otherwise_value)
=SIGN(number)
=ROW(reference)

=ARRAYFORMULA(                           // ARRAYFORMULA start
   VLOOKUP(                              // VLOOKUP start
     B2:B,                               // search_criterion                 
     DATA!A2:G,                          // array
                                         // index start
     IF(                                 // IF start
       C2:C="1st",                       // test 
         {4,5},                          // then_value
         {6,7}                           // otherwise_value
     ) *                                 // IF end
     SIGN(                               // SIGN start
       ROW(                              // ROW start
         A2:A                            // reference
       )                                 // ROW end
     ),                                  // SIGN end
                                         // index end
     0                                   // sort_order
   )                                     // VLOOKUP end
 )                                       // ARRAYFORMULA end

// to copy / paste
=ARRAYFORMULA(VLOOKUP(B2:B,DATA!A2:G,IF(C2:C="1st",{4,5},{6,7})*SIGN(ROW(A2:A)),0))

Explained

The SIGN and the ROW function are there to meet up with the criteria, set with using an ARRAYFORMULA. It will return an equally long array as setout in the search_condition. The IF function sets the condition for the index, by using the test.

Screenshot

enter image description here

Example

I've copied your file and added my solution to it: teacher references

Google Sheets – Automatically Wrap Text to New Responses from Form

The only way I found to do this is with a script.

Step 1: Go to Tools > Script Editor and put the following code there:

function wrap(e) {
  e.range.setWrap(true);
}

Step 2: Still in Script Editor, go to Resources > Current project's triggers, and select from dropdowns "wrap", "From spreadsheet", "On form submit".

NOTE: If under Resources you have no current project triggers - create a new one - name it "wrap" - then proceed to choose "From Spreadsheet", "On Form Submit" and it will work as stated.

Step 3: You're done. Close the Script Editor. The new form submissions will be automatically wrapped in the spreadsheet.

Best Answer

Related Solutions

Google Sheets – Add a Conditional Statement to a QUERY Function

Formula

Explained

Screenshot

Example

Google Sheets – Automatically Wrap Text to New Responses from Form

Related Topic