Forum Discussion
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 extracting and summing numbers.
Something like: TEXTJOINIFS(delim, empty_flag, criteria_ary1, criteria1, ...)
Where:
- 'delim' and 'empty_flag' work like the first two arguments of the current TEXTJOIN function
- the remaining arguments work like the typical xxxIFS functions.
Any suggestions who I might accomplish this using the current set of functions?
Thanks in advance for your help.
It could be
=TEXTJOIN(",",TRUE,IF(Booth_TBL[Company]=[@Company],Booth_TBL[Booth Label],""))if we speak about Booth Number(s) column
7 Replies
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.
It could be
=TEXTJOIN(",",TRUE,IF(Booth_TBL[Company]=[@Company],Booth_TBL[Booth Label],""))if we speak about Booth Number(s) column
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],""))
much more helpful to upload a sample of the spreadsheet you're working with, and perhaps an example of what you want the function to extract from a few columns in the real sheet. Otherwise we're all just dealing with an abstraction
