Combine Two Filter Functions

Occasional Contributor

I'd like to achieve the following:


Return columns from another sheet A | C | D | E | G (where A is between two figures) and then append (if populated) Y | Z or AA | AB or AC | AD or AE | AF or AG | AH or AI | AJ or AK | AL (Y | Z will always be populated).


So I might end up with the following dataset:

Row 01: A2 C2 D2 E2 G2 Y2 Z2

Row 02: A2 C2 D2 E2 G2 AA2 AB2

Row 03: A2 C2 D2 E2 G2 AH2 AI2
Row 04: A3 C3 D3 E3 G3 Y3 Z3

and so on..


I thought I could use the FILTER() function and combine what I need:

=FILTER(FILTER('FROM SCHEDULE'!A:Z,('FROM SCHEDULE'!A:A<9000)*('FROM SCHEDULE'!A:A>1500),""),{1,0,1,1,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1})

The above returns A C D E G Y Z and:

=FILTER(FILTER('FROM SCHEDULE'!A:AB,('FROM SCHEDULE'!A:A<9000)*('FROM SCHEDULE'!A:A>1500)*('FROM SCHEDULE'!AA:AA<>""),""),{1,0,1,1,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1})

returns A C D E G AA AB (where populated) - so I was thinking I could replicate this for AC AD, AE AF, AG AH etc..


So I can return the data I need, but I'd really like to merge these different commands together so everything is returned in to the same seven columns, I almost need something like:


=FILTER(FILTER('FROM SCHEDULE'!A:Z,('FROM SCHEDULE'!A:A<9000)*('FROM SCHEDULE'!A:A>1500),""),{1,0,1,1,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1}) & FILTER(FILTER('FROM SCHEDULE'!A:AB,('FROM SCHEDULE'!A:A<9000)*('FROM SCHEDULE'!A:A>1500)*('FROM SCHEDULE'!AA:AA<>""),""),{1,0,1,1,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1})

(though I appreciate just throwing an ampersand in won't actually do what I want, as I tried it ; ) ).


Is what I'm trying to do achievable (just with the right syntax) or am I approaching this from the wrong angle with FILTER() and would be better with a different approach?

I'd like the solution to update if my other sheet is amended.


Kind regards,


1 Reply

In case this isn't resolved and someone else comes across this, for now, I've simply put my several equations underneath each other with a few hundred rows in between to allow the spill ranges to grow (they shouldn't need anywhere near that space, but, best be safe), hidden these columns and added another filter that returns these columns with the blank rows omitted (with a sort on the first thrown in for good measure) and this has achieved what I need, if feeling like a bit of a cheat.


Would ideally like to know the best way to merge two+ spills though.