0

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

enter image description here

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.

0

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

enter image description here

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

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

1
  • Have you tried the given solution? Did it work for you? If not we could try finding out why. If yes, please remember that as per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. – marikamitsos Oct 28 '20 at 18:11

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.