Forum Discussion

JBF_54's avatar
JBF_54
Brass Contributor
Jan 28, 2020
Solved

Need to build a string based on conditions

I have need to scan various columns of data within a table and, depending on certain criteria, extract a field. Something like SUMIFS, except extracting and concatenating strings rather than extracti...
excel
Formulas and Functions
office 365
JBF_54's avatar
JBF_54
Brass Contributor
Jan 28, 2020

SergeiBaklan

 

How elegant ... thanks very much.

 

To test multiple conditions I modified the formula (highlighted in red), and of course, I populated the Tables Needed column with some data. It returned nulls in all cases. Would you have expected this to work?

 

=TEXTJOIN(", ",TRUE,IF(AND(Booth_TBL[Tables Needed]>0,Booth_TBL[Company]=[@Company]),Booth_TBL[Booth Label],"")) 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jan 28, 2020

    JBF_54 

    Nope, that doesn't work since AND() returns only first element of the array with conditions. Instead use equivalent formula

    =TEXTJOIN(", ",TRUE,IF((Booth_TBL[Tables Needed]>0)*(Booth_TBL[Company]=[@Company]),Booth_TBL[Booth Label],""))

     

    • JBF_54's avatar
      JBF_54
      Brass Contributor
      Jan 28, 2020

      SergeiBaklan

       

      Ok ... let me make sure I understand what's going on

      (You are about to help me get my level of Excel thinking to the next plateau!)

      • AND is not an array function ... so don't use it (for this problem)
      • We're relying on the facts that numerically FALSE is 0 (zero) and TRUE is 1 (one)
      • For each condition I'm concerned with, a TRUE or FALSE is returned for each array element
      • By multiplying them together, if any one condition is FALSE (zero) then the entire set of conditions becomes FALSE because zero times anything is zero.
      • So, drop the 'AND', and separate the conditions with asterisks instead of commas.
      • If I need OR'd conditions, then add rather than multiply those conditions and compare the results to >0.

      And a door has opened to help me (re)solve a bunch of stuff.

       

      I'll guess that if I want to flip the results of a condition I'll have to avoid the use of the NOT function.

       

      Thanks very much again,

       

      Your padawan learner

       

       

       

       

       

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jan 28, 2020

        JBF_54 

        Yes, exactly. Some comments about latest two

         

        - more correctly instead of few conditions combined by AND(condition1, condition2) we combine them by multiplication as condition1*condition2;

        - Yes, since FALSE is zero and any non-zero number is considered as TRUE,  instead of OR(condition1, condition2) we may use (condition1+condition2) 

Resources