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

JBF_54 OK ... I uploaded a sample spreadsheet.

The general back story is that my group puts on a fishing show and rents booths to vendors. In an effort to get rid of some manual (error prone) data entry, I would like to find a way for a field in a Vendor record (1 record per vendor) to look into another table containing Booth records (1 record per booth) and build a list of Booth Numbers that the Vendor has rented.

  • 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

         

         

         

         

         

Resources