Aug 05 2022 08:42 AM
I'm not sure if I'm missing something, or just have a prolonged brain freeze...
Using the most recent version of 365, including beta functions.
Say I have this:
Simple (?) question: what function would return "Find me"? Not an array, not a column or row, just the content of the cell which is not "xxx".
Does the answer change if it looks like this:
and I want both cells that are not "xxx"?
Finally, does the answer change if instead of "xxx" the relevant cells were blank or FALSE, for example, if the array is the output of some function (see attached file)?
Dazed and confused...
Aug 05 2022 09:28 AM - edited Aug 05 2022 10:25 AM
Solution
Just an idea:
E1:
=TEXTJOIN("; ",TRUE,IF(A1:C3<>"xxx",A1:C3,""))
E5:
=TEXTJOIN("; ",TRUE,IF(A5:C7<>"xxx",A5:C7,""))
E9:
=TEXTJOIN("; ",TRUE,IF(A9:C11<>"",A9:C11,""))
E13:
=TEXTJOIN("; ",TRUE,IF(A13:C15<>FALSE,A13:C15,""))
Wrap above formulas in Beta TEXTSPLIT function if you want the values in columns/rows
Aug 06 2022 05:20 AM
Aug 06 2022 06:34 AM
It mainly depends on which form you'd like to have an answer. As variant
=LET( exclude, "xxx", c, TOCOL(A1:C3), FILTER( c, c <> exclude ) )
Aug 05 2022 09:28 AM - edited Aug 05 2022 10:25 AM
Solution
Just an idea:
E1:
=TEXTJOIN("; ",TRUE,IF(A1:C3<>"xxx",A1:C3,""))
E5:
=TEXTJOIN("; ",TRUE,IF(A5:C7<>"xxx",A5:C7,""))
E9:
=TEXTJOIN("; ",TRUE,IF(A9:C11<>"",A9:C11,""))
E13:
=TEXTJOIN("; ",TRUE,IF(A13:C15<>FALSE,A13:C15,""))
Wrap above formulas in Beta TEXTSPLIT function if you want the values in columns/rows