Filter by merged cells in Google Sheets?

Question

I need to filter a data table by date values. Unfortunately the date cells are merged like so:

So if I would write

=FILTER(B1:B; A1 >= DATE(2020, 4, 1))

I only get every other value in Column B. I guess I should combine two filters, one of which has the source offset by one, but then I'd get a range mismatch error, because the source range would be one row shorter than the one in the condition statement.

Answer 1

You can try the following

=FILTER(A1:B21, 
            ArrayFormula(vlookup(ROW(1:21),{IF(LEN(A1:A21)>0,ROW(1:21),""),A1:A21},2)) >= DATE(2020, 10, 30))

What we do is create a virtual column for column A.

(Try the partial formula by itself and see how it behaves.)

Answer 2

As I do not have enough reputation to comment the other answer I'm adding a new one. The formula from the previous answer from @marikamitsos works but for a different locale you need to use:

=FILTER(A1:B21; 
            ArrayFormula(vlookup(ROW(1:21);{IF(LEN(A1:A21)>0;ROW(1:21);"")\A1:A21};2)) >= DATE(2021; 10; 30))