Another Excel TEXTJOIN Example

Quote of the Day

If you haven't read hundreds of books, learning from others who went before you, you are functionally illiterate – you can't coach and you can't lead.

— Jim Mattis, scholar general

Figure 1: Glass-Encased Fuse. (Wikipedia)

I have been analyzing seemingly random fuse failures in different products. The failing fuse is similar to the unit shown in Figure 1. My analysis has shown that the fuses are failing because of damage they are sustaining during the manufacturing process. In my analysis report, I was asked to list each product that experienced a fuse failure and to list how many days each product was in service before the fuse failure occurred. Because of the large number of products and failures, I wanted to find a table format that would contain the required information in a condensed format. Excel's TEXTJOIN function and pivot table feature solved my problem nicely. For confidentiality and presentation reasons, my example here will contain simulated failure data that is MUCH shorter than the actual data file.

Figure 2 shows an abbreviated version of my test results table that contains the RMA number, failed assembly name, and the number of days the assembly was in service. I appended a column to this table that contains the Excel function:

=TEXTJOIN(",",TRUE,IF(C17=[Assembly],[Days in Service],""))

This function creates a comma-separated values list of all the "Days in Service" values associated with the failed assemblies. I then used a pivot table to generate my final summary table. The entire process is shown in this Excel workbook, which contains a macro from the Contextures web site that turns off the filter arrows.

Figure 2: Illustration of My Table Transformation.