Advanced FILTER Function to Extract Data Subject to Conditions

[andrewc]

Hi,

In A1:K16 I have sample data from which I want to extract the results in A22:K27.

A product code will only have a row in the results if the sign (positive or negative) of the value in column F differs from that in column G AND/OR the sign of the value in column J differs from that in column K.

For example:

Product AAA is included in the results because the sign of its value in F2 is positive while its value in G2 is negative
Product EEE is included in the results because the sign of its value in J6 is negative while its value in K6 is positive.
H22:K22 and D23:G23 are blank for AAA and EEE, respectively, because the sign of their values in J2 and K2 and in F6 and G6 are the same.
BBB is not among the results as both of the sign of its values in F3 and G3 and in J3 and K3 are the same.

Can someone please suggest a FILTER function formula to achieve the results in A22:K27 (or some other solution)?

Thanks!

[Bo_Ry]

Please try

=LET(z,A1:K16,a,(INDEX(z,,6)>0)*(INDEX(z,,7)<0),c,(INDEX(z,,10)<0)*(INDEX(z,,11)>0),f,FILTER(z,a+c),h,LEFT(INDEX(z,1,)),
IF((1-(INDEX(f,,6)>0)*(INDEX(f,,7)<0))*(h="1")+(1-(INDEX(f,,10)<0)*(INDEX(f,,11)>0))*(h="3"),"",f))

[Glenn Kennedy]

Nice one, Bo_Ry. However, does it work if you change the signs in (for example) F2 & G2 to -1 and +2. That still meets the ctiteria, but AAA vanishes from the results.

I have it working here as per the criteria... but am struggling to make the formula dynamic!!!


=FILTER(A2:A16,((SIGN(F2:F16)<>SIGN(G2:G16)+(SIGN(J2:J16)<>SIGN(K2:K16)))))
which is OK..

and
=IFERROR(FILTER(INDEX(D:G,IF(SIGN($F$2:$F$16)<>SIGN($G$2:$G$16),ROW($A$2:$A$16),""),SEQUENCE(,4)),$A$2:$A$16=A30),"")

=IFERROR(FILTER(INDEX(H:K,IF(SIGN($J$2:$J$16)<>SIGN($K$2:$K$16),ROW($A$2:$A$16),""),SEQUENCE(,4)),$A$2:$A$16=A30),"")

which are dynamic only in one direction. I can't get them fully dynamic....

[Bo_Ry]

Thanks, Glenn.

For change the signs

=LET(z,A1:K16,f,FILTER(z,(N(+INDEX(z,,6))*N(+INDEX(z,,7))<0)+(N(+INDEX(z,,10))*N(+INDEX(z,,11))<0)),h,LEFT(INDEX(z,1,)),
rs,IF((1-(INDEX(f,,6)*INDEX(f,,7)<0))*(h="1")+(1-(INDEX(f,,10)*INDEX(f,,11)<0))*(h="3"),"",f),rs)

[XLent]

first, the below is no better than Bo_Ry's suggestion indeed most of it is the exact same (copied) logic -- the only real differences being

the booleans for 1y + 3y are held separately (a & b) and then a CHOOSE is applied relative to header to determine which of the a/b booleans to use when determining whether or not to return the value from the original source (for non 1/3 headers default of TRUE)

=LET(src,A1:K16,a,(N(+INDEX(src,0,6))*N(+INDEX(src,0,7)))<0,b,(N(+INDEX(src,0,10))*N(+INDEX(src,0,11)))<0,c,IF(CHOOSE(IFERROR(LEFT(INDEX(src,1,0))+0,2),a,TRUE,b),src,""),FILTER(c,a+b))

[Glenn Kennedy]

And... another option. No better/worse that either of its predecessors... but... I can understand this formula more easily.

=LET(a,A1:K1,b,A2:K16,c,SIGN(INDEX(b,,6))<>SIGN(INDEX(b,,7)),d,SIGN(INDEX(b,,10))<>SIGN(INDEX(b,,11)),FILTER(IF(CHOOSE(IFERROR(LEFT(INDEX(a,1,0))+0,2),c,TRUE,d),b,""),c+d))

For me, the use of SIGN is a more understandable argument than the argument used by Bo_Ry (and copied by Xlent). My main reason for battering on with this (while simultaneously brewing a 25L batch of beer) was that I don't like being beaten. So, it's a combination of my initial effort (that worked non-dynamically) and the efforts of both Xlent & Bo_Ry!

[andrewc]

Thank you all very much!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.