Excel - Create a string from a table

Question

i'd like to create a string from a table, something similar to this example:

	Passed	Failed	Skipped
Test1	13	4	10
Test2	10	12
Test3	20		9
Test4		25	11

From this table i'd like to have this strings like this

	String
Test1	13 Passed - 4 Failed - 10 Skipped
Test2	10 Passed - 12 Failed
Test3	20 Passed - 9 Skipped
Test4	25 Failed - 11 Skipped

Answer 1

You need TEXTJOIN() function. Try below formula-

=TEXTJOIN("-",TRUE,IF(($B$2:$D$5<>"")*($A$2:$A$5=F2),$B$2:$D$5&" " &$B$1:$D$1,""))

Note: TEXTJOIN() is only available to Excel-2019 and Excel365. For older version you need Textjoin UDF.

Here is TEXTJOIN user defined function link by Scott Craner