1

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.

qazamy
  • 47
  • 7

1 Answers1

1

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?

JPV
  • 26,499
  • 4
  • 33
  • 48
  • Thanks @JPV ! Both work great. I'll be using option 2 as I understand REGEXMATCH less than I understand ARRAYFORMULA+QUERY. – qazamy Aug 18 '21 at 16:23
  • Most welcome, @qazamy ! You may also want to consider [accepting the answer](https://stackoverflow.com/help/accepted-answer) if this post helped you solve your issue. – JPV Aug 18 '21 at 18:39
  • done! @JPV by the way, could you help me take a look at my other issue pertaining using ARRAYFORMULA with custom function? - https://stackoverflow.com/questions/68762397/arrayformula-not-working-with-custom-formula-containing-if-and-countif – qazamy Aug 19 '21 at 11:19