Google Sheets Query Function Without Blank Rows

google sheetsgoogle-sheets-query

I am trying to combine multiple sheet with this query function:

=query({'LJK 1'!C3:C,'LJK 1'!D3:D,'LJK 1'!E3:E,'LJK 1'!F3:F,'LJK 1'!G3:G,'LJK 1'!H3:H,'LJK 1'!I3:I,'LJK 1'!J3:J,'LJK 1'!K3:K,'LJK 1'!L3:L,'LJK 1'!M3:M,'LJK 1'!N3:N,'LJK 1'!P3:P;'LJK 2'!C3:C,'LJK 2'!D3:D,'LJK 2'!E3:E,'LJK 2'!F3:F,'LJK 2'!G3:G,'LJK 2'!H3:H,'LJK 2'!I3:I,'LJK 2'!J3:J,'LJK 2'!K3:K,'LJK 2'!L3:L,'LJK 2'!M3:M,'LJK 2'!N3:N,'LJK 2'!P3:P})

It combines them all in one sheet. The problem is, it also combines the empty row, so the combined sheet doesn't look so good. How to query without those blank rows?

Best Answer

You either add another column and use a filter like this :

=filter( A:A, A:A<>"")

Or directly inside your function :

=filter(
query({'LJK 1'!C3:C,'LJK 1'!D3:D,'LJK 1'!E3:E,'LJK 1'!F3:F,'LJK 1'!G3:G,'LJK 1'!H3:H,'LJK 1'!I3:I,'LJK 1'!J3:J,'LJK 1'!K3:K,'LJK 1'!L3:L,'LJK 1'!M3:M,'LJK 1'!N3:N,'LJK 1'!P3:P;'LJK 2'!C3:C,'LJK 2'!D3:D,'LJK 2'!E3:E,'LJK 2'!F3:F,'LJK 2'!G3:G,'LJK 2'!H3:H,'LJK 2'!I3:I,'LJK 2'!J3:J,'LJK 2'!K3:K,'LJK 2'!L3:L,'LJK 2'!M3:M,'LJK 2'!N3:N,'LJK 2'!P3:P})
, query({'LJK 1'!C3:C,'LJK 1'!D3:D,'LJK 1'!E3:E,'LJK 1'!F3:F,'LJK 1'!G3:G,'LJK 1'!H3:H,'LJK 1'!I3:I,'LJK 1'!J3:J,'LJK 1'!K3:K,'LJK 1'!L3:L,'LJK 1'!M3:M,'LJK 1'!N3:N,'LJK 1'!P3:P;'LJK 2'!C3:C,'LJK 2'!D3:D,'LJK 2'!E3:E,'LJK 2'!F3:F,'LJK 2'!G3:G,'LJK 2'!H3:H,'LJK 2'!I3:I,'LJK 2'!J3:J,'LJK 2'!K3:K,'LJK 2'!L3:L,'LJK 2'!M3:M,'LJK 2'!N3:N,'LJK 2'!P3:P}) <> "")

Note : you can replace {'LJK 1'!C3:C,'LJK 1'!D3:D, .... } with =flatten('LKJ 1'!C3:P) so your function become

=filter ( 
{flatten('LKJ 1'!C3:P);flatten('LKJ 2'!C3:P)}, 
{flatten('LKJ 1'!C3:P);flatten('LKJ 2'!C3:P)}<>""
)
Related Topic