CODEWITHSUNDEEP
excel
Zils
Zils

Reputation: 405

Excel get multiple cell value as comma seperated array

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

Upvotes: 0

Views: 56

Answers (1)

Scott Craner
Scott Craner

Reputation: 152465

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)

enter image description here

Upvotes: 2

Related Questions