Forum Discussion
Solved
Return multiple strings to a single cell based on conditional criteria
Hello, I am trying to create a column that returns strings from multiple columns to a single cell, based on an associated condition from yet another set of columns. Apologies if my language is no...
In I2, if the TRUE values in columns E to G are 'real' TRUE/FALSE values:
=TEXTJOIN(", ", TRUE, FILTER(B2:D2, E2:G2, ""))
If the TRUE values in columns E to G are text values:
=TEXTJOIN(", ", TRUE, FILTER(B2:D2, E2:G2="TRUE", ""))
Try this:
- Create a Helper Column:
- Insert a new column (let's say Column H) to help concatenate the dominant species for each data point.
- Use the TEXTJOIN Function:
- In the helper column, use the TEXTJOIN function combined with IF statements to check if the species is dominant and concatenate them.
Here's an example formula you can use in the helper column (H):
=TEXTJOIN(", ", TRUE, IF(E2=TRUE, B2, ""), IF(F2=TRUE, C2, ""), IF(G2=TRUE, D2, ""))
This formula checks each "Dom" column (E, F, G) for TRUE and includes the corresponding species (B, C, D) if it is dominant. The TEXTJOIN function then concatenates these species with a comma and space as the delimiter.
- Copy the Formula Down:
- Drag the formula down the helper column to apply it to all rows.
- Move the Helper Column to the Desired Location:
- If you want the result in Column I, you can copy the helper column and paste it into Column I.
Thank you for your reply - this works great and is in line with what I was trying to write myself. Much appreciated!!
