# Combine Two Filter Functions

Occasional Contributor

# Combine Two Filter Functions

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,

Sam

8 Replies

# Re: Combine Two Filter Functions

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.

# Re: Combine Two Filter Functions

Hi @SamB513 have you tried using the vstack function?

# Re: Combine Two Filter Functions

play with sql may be easier. for e.g. filter 1 between 5 raw table: F_A F_B F_C F_D F_E 1 B2 C2 E2 F2 5 B3 C3 E3 F3 3 B4 C4 E4 F4 9 B5 C5 E5 F5 select * from filter_and_append_different_column; //select * from filter_and_append_different_column where F_A between 1 and 5; select F_A,F_B,F_C from filter_and_append_different_column where F_A between 1 and 5 union all select F_A,F_D,F_E from filter_and_append_different_column where F_A between 1 and 5;result

``````select * from filter_and_append_different_column; //select * from filter_and_append_different_column where F_A between 1 and 5; select F_A,F_B,F_C from filter_and_append_different_column where F_A between 1 and 5 union all select F_A,F_D,F_E from filter_and_append_different_column where F_A between 1 and 5;
``````

: F_A F_B F_C 1 B2 C2 5 B3 C3 3 B4 C4 1 E2 F2 5 E3 F3 3 E4 F4

# Re: Combine Two Filter Functions

@cb147852369 thanks, vstack never came up in my searches, will look in to it!

# Re: Combine Two Filter Functions

a lot of things in life would be easier if we were allowed to use SQL ; ) - thanks for the effort but I'm restricted to Excel for this one.

# Re: Combine Two Filter Functions

Hi @SamB513

I'm restricted to Excel for this one

If a Power Query (Excel >= 2016 on Windows) option fits into your restrictions I'll be glad to look at a possible solution. However I'll appreciate you upload & share (i.e with OneDrive or any other file sharing service) a sample workbook reflecting your source data + the expected result as I must admit I'm not clear at all re. what you ultimately expect

# Re: Combine Two Filter Functions

@SamB513 I think they only released it to excel a few months ago so it wouldn’t have been coming up as an option  back in May.

# Re: Combine Two Filter Functions

@SamB513 never mentioned which Excel's version they're working with nor the OS

FILTER is avail. on 2021 and 365 but VSTACK, HSTACK and the like are only available with 365