Forum Discussion
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
9 Replies
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
- 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 https://support.microsoft.com/en-us/office/share-onedrive-files-and-folders-9fcc2f7d-de0c-4cec-93b0-a82024800c07 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
Hi SamB513 have you tried using the vstack function?
cb147852369 thanks, vstack never came up in my searches, will look in to it!
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.
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.
