QUERY formula returns #N/A error “Query completed with an empty output.”

user_16575813 0
Viewed 408

sample sheet - https://docs.google.com/spreadsheets/d/1RPpwHAtER05h7-K1Hxku4lz33kBI4KnL7kzJa63YuvY/edit?usp=sharing

Having issues with using =query() in cell A10 on a column that has data with dates and texts ('raw A'!A). Any idea how do I go about this? Data in "raw A" and raw B" tab would not be editable or changeable.

=QUERY('raw A'!A:M,"SELECT A,E,F,G,H,I,J,K,L,M WHERE A CONTAINS 'summary' AND not C CONTAINS 'ALL'",0)

The idea is to allow users to import raw data sheets into this template sheet and have the "result" tab auto retrieve the filtered data and display it in a refined layout.

google-sheets-formula google-sheets
1 Answers

Query has issues with mixed data types in the same column. The query() you use for 'raw B' works because you don't have those mixture there. For 'raw A' you can use

=filter({'raw A'!A:A, 'raw A'!E:M}, regexmatch('raw A'!A:A, "summary"), not(regexmatch( 'raw A'!D:D, "ALL")))

or

=ArrayFormula(query({'raw A'!A:A&"", 'raw A'!B:M}, "SELECT Col1,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13 where Col1 = 'summary' and Col4 <>'ALL'", 0))

The latter will convert the column A to text before the query. I hope that helps?

user_38894230
Related