1

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?

1 Answer 1

0

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)}<>""
)
2
  • im using inside the function directly but i got value! it says filter range must be a single row or a single column, how to fix this?
    – Fuze A.
    Jan 6, 2021 at 4:02
  • My bad, replace the commas here flatten(...) , flatten (..) with a ;
    – pjmg
    Jan 6, 2021 at 10:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.