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,

Sam

8 Replies

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.

Hi @SamB513 have you tried using the vstack function?

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;

Screenshot_2022-12-16-13-01-10-178_cn.uujian.browser.jpg

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

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

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.

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

@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. 

@cb147852369 

 

@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