Forum Discussion
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 not correct, I have very little experience with excel formulas.
In my example below, for each Data Point (Column A), there are three columns for plant species (Columns B, C, D), which contain a text string, represented by a letter. Not all Data Points have 3 plant species. If a plant species is present, it is either dominant, represented by TRUE in the "Dom" columns (E, F, G), or not dominant, which is a blank cell.
Column I is the output I want. I want each cell in the "Dominant Species" column to return a list of the dominant plants for that data point, separated by commas. I have manually input the result I'm looking for in the screenshot.
I have looked into IF statements, TEXTJOIN, and H/V LOOKUP functions, but I can't work out how to get what I need. Any advice? Functions to try? I don't really have a complete grasp of the function options available to me.
For more context, this data is an output from an app used to collect environmental field data, and I'd like to be able to use a function to acquire the dominant species without changing the output table. The example below is a representation of what I need - in reality there are 30 plant columns, with the scientific name of a plant in contained in some (but not all) of the cells.
Thank you!!!!
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", ""))
4 Replies
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!!
- Create a Helper Column:
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", ""))
The first solution worked, amazing!! Thank you for your quick and helpful reply!
