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: stackoverflow.com/questions/44706211/… — Scott Craner
– Scott Craner, Commented Mar 28, 2018 at 13:07
or here for a UDF that mimics the TEXTJOIN: stackoverflow.com/questions/39532189/… — Scott Craner
– Scott Craner, Commented Mar 28, 2018 at 13:09

Xavier Guihot · Accepted Answer · 2018-03-29 07:45:28Z

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)

Xavier Guihot

62.8k26 gold badges320 silver badges202 bronze badges

answered Mar 28, 2018 at 12:57

Scott Craner

154k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thanks, Any way on Office 2007?