List Failed Inspection Items
I need to create a comma-separated string of all items that failed the quality check for the report.
=TEXTJOIN(", ", TRUE, IF(B2:B7="Failed", A2:A7, "")) How it works: This formula leverages TEXTJOIN's ability to concatenate multiple text strings with a specified delimiter. The core of the solution is the IF statement, which acts as a filter. It iterates through the 'Status' column (B2:B7) and, for every row where the status is 'Failed', it retrieves the corresponding 'Inspection Item' from column A (A2:A7). If the status is not 'Failed', it returns an empty string. TEXTJOIN then takes this array of items (some real, some empty) and, because the 'ignore_empty' argument is set to TRUE, it only joins the actual failed items, effectively creating your desired comma-separated list.
Data Setup
| Inspection Item | Status |
|---|---|
| Foundation Check | Passed |
| Roofing Installation | Failed |
| Plumbing Rough-in | Passed |
| Electrical Wiring | Failed |
| HVAC Ductwork | Passed |
| Window Sealing | Failed |
Step-by-Step Guide
Organize your inspection data with 'Inspection Item' in one column (e.g., Column A) and 'Status' (e.g., 'Passed', 'Failed') in an adjacent column (e.g., Column B).
Select an empty cell where you want the comma-separated list of failed items to appear.
Enter the TEXTJOIN function, specifying the delimiter (e.g., ", "), setting 'ignore_empty' to TRUE, and then using an IF statement to filter.
The IF statement `IF(B2:B7="Failed", A2:A7, "")` checks each status. If it's 'Failed', it returns the corresponding item from Column A; otherwise, it returns an empty string.
TEXTJOIN then concatenates only the non-empty strings (the failed items) with the specified delimiter.