FIlter function on two areas

Question

Trying to filter out data from one worksheet to another.

Example of the layout:

In one sheet I filter out the B data, which is continuous, by simply finding the start of the set with =MATCH("B";Sheet1!A:A;0) in a helper column, and then fill down with =INDIRECT("Sheet1!A"&$A$5+ROW(B1)).

I know this technically doesn't work with the example data, but Imagine that A7 contains the text "B" and the A data ends at row 6.

This might not be the most elegant solution, but it works.

I'm trying to get the A data – but only the rows with a date – as a continuous list in the third sheet.

I found the "new" FILTER() function, which works well for a part of it, either with:

=FILTER(Sheet1!A1:B7; (Sheet1!B1:B7>1;"")

or

=FILTER(Sheet1!A13:B20; (Sheet1!B13:B20>1;"")

Is there a way to combine or get both these filtered lists after each other, or do I have to use another approach?

I know I could do it with a macro, but I'm trying to keep this one VBA-free for once, since it's to be used in broswer, but functions aren't my strong suit.

user_4617121 · Answer

Using your example FILTER statements, perhaps you could do:

=LET( range, Sheet1!A1:B20,
      excludeRange, Sheet1!A8:B12,
       FILTER(range,(Sheet1!B1:B20>1)*ISNA(MATCH(ROW(range),ROW(excludeRange),0)),"") )

Where you put in your continuous range where I put in range (Sheet1!A1:B20) and your excluded B range in excludeRange (Sheet1!A8:B12). Sheet1!B1:B20 could also be parameterized, but I think you get the picture.

FIlter function on two areas

1 Answers