I'm trying to do a FILTER considering to include a match on several columns (whose number is not predefined at start). Explaining...
Consider the following table:
KEY | COL1 | COL2 | COL3 |
---|---|---|---|
A | XXXX | YYYY | ZZZZ |
B | YYYY | XXXX | |
C | ZZZZ |
I want to FILTER when COL1 OR COL2 OR COL3 contain, for instance, "XXXX". The result here would be:
KEY |
---|
A |
B |
I know I can do the following, and it works:
- =FILTER(A1:A4;(B1:B4="XXXX") (C1:C4="XXXX") (D1:D4="XXXX");"")
The question is that the number of columns COL can grow/shrink and one would need to add many OR conditions on the include which is non practical.
I was expecting to be able to do something like the following:
- =FILTER(A1:A4;B1:D4="XXXX";"")
But this does not work as the FILTER include parameter needs to have size 1 column per 4 rows in order to operate.
I understand that, to do this, I might "just" need to create an array with TRUEs/FALSEs that can then be used in the include of the FILTER function. But my Excel skills do not go so deep :)
Question: how to I do this in one Excel formula ?
CodePudding user response:
I think you were headed in the right direction. B1:D4="XXXX"
gets you to a 3 column by 4 row array of TRUE and FALSE values. What you're wanting to do is then perform a "row-wise OR of all the values in each row, one row at a time." So... Let's actually do just that, a row-wise OR of each row:
=BYROW(B1:D4="XXXX",LAMBDA(r,OR(r)))
This will result in a column array like {TRUE;TRUE;FALSE;FALSE}
where TRUE means one or more matches and FALSE means no matches. In this case, rows 1 and 2 had matches, row 3 and 4 did not.
BYROW steps through each row of a two-dimensional array, and passes the row to a lambda function. (Note that in the BYROW/BYCOL functions, the LAMBDA is passed the entire row content as an array. This is in contrast to a function like MAKEARRAY where the "r" value passed to the LAMBDA is simply the current row number and not the content.)
Then:
=FILTER(A1:A4,BYROW(B1:D4="XXXX",LAMBDA(r,OR(r))))
Now you can just edit A4 and D4 to be what you need and you're off and running.
If this filter is going to become part of a more complicated formula, consider:
=LET(
matchingRows, BYROW(B1:D4="XXXX",LAMBDA(r,OR(r))),
matchingValues, FILTER( A1:A4, matchingRows ),
// then use "matchingValues" in another formula //
)
CodePudding user response:
I think you're trying to filter out the values if any of the columns contain value "XXXX" in the same row. In that case you could use:
=FILTER(A2:A4,MMULT(--(B2:D4="XXXX"),SEQUENCE(ROWS(B2:D4),,1,0)))
Or using BYROW
:
=FILTER(A2:A4,BYROW(B2:D4,LAMBDA(x,COUNTIF(x,"XXXX"))))
Or more dynamically;
=LET(range,A2:D4,
key,INDEX(range,,1),
cols,INDEX(range,SEQUENCE(ROWS(range)),SEQUENCE(1,3,2)),
FILTER(key,MMULT(--(cols="XXXX"),SEQUENCE(ROWS(cols),,1,0))))