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],""))

         

Resources