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?

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 at 4:02
  • My bad, replace the commas here flatten(...) , flatten (..) with a ;
    – pjmg
    Jan 6 at 10:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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