1

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.

2 Answers 2

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

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. Oct 28, 2020 at 18:11
-1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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