Google Sheets – FILTER or QUERY Multiple Ranges Without Blanks

google sheetsgoogle-sheets-query

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

Best Answer

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))
Related Topic