Compile Ticket Notes
I need to combine all 'Comment' entries for Ticket #1234 into a single history log.
=TEXTJOIN(" | ", TRUE, IF(A2:A6=1234, B2:B6, "")) How it works: This formula leverages TEXTJOIN's ability to concatenate text strings with a specified delimiter. The crucial part is the `IF` statement, which acts as a filter. It creates an array of comments, but only includes those where the 'Ticket ID' matches '1234'. For all other Ticket IDs, it inserts an empty string. TEXTJOIN then processes this array, using `TRUE` for its `ignore_empty` argument to skip these empty strings, ensuring only relevant comments are joined and preventing unnecessary delimiters.
Data Setup
| Ticket ID | Comment | |
|---|---|---|
| 1234 | "Customer called regarding slow internet." | |
| 5678 | "Followed up on previous issue." | |
| 1234 | "Performed remote diagnostic | found no issues." |
| 9012 | "New service request." | |
| 1234 | "Advised customer to restart router | issue resolved." |
Step-by-Step Guide
Ensure your data is organized with 'Ticket ID' in column A and 'Comment' in column B.
Identify the specific Ticket ID for which you want to compile notes (e.g., 1234).
In an empty cell, enter the formula: `=TEXTJOIN(" | ", TRUE, IF(A2:A6=1234, B2:B6, ""))`.
The `" | "` acts as the delimiter between each comment.
`TRUE` tells TEXTJOIN to ignore any empty cells that result from the IF condition.
`IF(A2:A6=1234, B2:B6, "")` checks each row in column A. If the 'Ticket ID' matches '1234', it includes the corresponding 'Comment' from column B; otherwise, it returns an empty string.
Press Enter. Excel will automatically combine all comments for Ticket #1234 into a single cell, separated by ' | '.