Forum Discussion
Solved
Find one or more scattered cells in a range
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: wh...
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
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
So it was a brain freeze after all - and TEXTJOIN() was the answer. It feels a little hacky - I thought there would be some magic function to pinpoint a particular cell in an array if that cell meets/doesn't meet certain conditions, but I guess Excel isn't there yet...
But it does work - and thanks!
But it does work - and thanks!
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 ) )
