2

I am trying to query or filter multiple ranges but am running into a situation with blank cells as I need all the ranges merged without blanks inbetween. Here is the sheet which has both my query and filter attempts.

Sample source data:

WA110862 question example

    A       B    C     D    E 
1
2   RANGE 1 JIM  JON  STAN  BOB
3   RANGE 2 JOE  LUKE MEL   
4   RANGE 3      DAVE DON   
2

QUERY has a inbuilt option to remove blanks:

=TRANSPOSE(QUERY(TRANSPOSE({B2:E2,B3:E3,B4:E4}),"select * where Col1 is not null"))

This is a alternative solution, which does not require you to list all the ranges (useful, if there are 1000s of ranges)

=ARRAYFORMULA(SPLIT(TRIM(CONCATENATE(QUERY(B2:E4&" ",, 50000)))," ", 0))
1

Please try:

=split(textjoin("|",1,QUERY({B2:E2,B3:E3,B4:E4},"select *")),"|")

TEXTJOIN has the option to exclude blank cells when concatenating them and the string can then be separated with SPLIT.

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.