Monday, October 23, 2017

Using QUERY to merge sheets

Interesting article :

"=QUERY({'Alexis Form'!A:M;'Anna Form'!A2:M;'Linda Form'!A2:M;'Sierra Form'!A2:M},"Select * where Col2 <> '' ",1)

...the formula will QUERY all the sheets and pull all ( the * means all) data where Col2 (B in the sheets where data is text) does not equal blank. Notice the ranges where the first sheet has A:M but the rest has A2:M, this is so the QUERY will only pull one set of headers, if you use A:M on all them then you will end up with 4 sets of headers with each above it's data.

In a normal QUERY formula using only one sheet then cell referencing uses the columns letter (A, B, C, etc), but when querying multiple sheets inside an {array} then you have to use Col# referencing."