Excel get multiple cell value as comma seperated array

Question

Here is my Table

Roll Result
1    Passed
2    Failed
3    Passed
4    Failed
5    Failed

on C1 cell how can I get all failed rolls as comma separated value? Expected result on C1 cell is- 2,4,5

for a vba approach see here: https://stackoverflow.com/questions/44706211/excel2011-vlookup-and-combine — Scott Craner, Mar 28 '18 at 13:07
or here for a UDF that mimics the TEXTJOIN: https://stackoverflow.com/questions/39532189/vlookup-with-multiple-criteria-returning-values-in-one-cell — Scott Craner, Mar 28 '18 at 13:09

score 2 · Accepted Answer · edited Mar 29 '18 at 07:45

2

Office 365 Excel has TEXTJOIN(), use it as an array formula

=TEXTJOIN(",",TRUE,IF(B2:B6="Failed",A2:A6,""))

Being an array formula it must be enter with Ctrl+Shift+Enter instead of Enter.

Earlier versions:

Put this in C2 and copy down the length of the data. C2 will return your value after the whole column is filled with the formula:

=IF(B2="Failed",A2&IF(C3="","",",") &C3,C3)

edited Mar 29 '18 at 07:45

Xavier Guihot

answered Mar 28 '18 at 12:57

Scott Craner

1 Answers1