Combine Two Filter Functions

[SamB513]

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

[#2]

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.

[#3]

Hi @SamB513 have you tried using the vstack function?

[#4]

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

[#5]

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

[#6]

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.

[#7]

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

[#8]

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

[#9]

@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

[#10]

To be clear, VStack can only be entered as a function in Excel online in a browser. Once entered, the results can be seen when using Excel on the Desktop. There are some caveats, however. For instance, if you use a VStack function in a conditional format, you would of course enter that conditional formula in Excel online. Once you opened that file in Excel Desktop, and entered data that would trigger the conditional format, the format would not visibly change in the desktop edition and no errors are thrown. You can actually have the browser version and the desktop version both open at the same time and, while entering data in either shows updates in the other, the conditional formatting only shows its results in the browser version.