Convert array to string via formula

[CodeNinja]

I have a series of if/then conditions I need to concatenate... Rather than have a long formula of concatenate(if(a2="fail",a1,""),if(b2="fail",b1,""),......) I would like to do an array formula {=Concatenate(if(A2:R2="Fail",$C$1:$R$1,""))} Unfortunately concatenate only sees the first result in the array... When I evaluate the if then portion, I get {"","",Cell$C$1,"",""...) but the concatenation will not joint them together. I tested this by using the formula {=concatenate({"Yes","No"})} and got Yes as my answer...

Any ideas how to make this work the way I want?

Thanks,

CN.

 

[CodeNinja]

Nevermind, I just wrote a user defined function that handles this...

Function cCat(rngTest As Range, sCondition As String, rngResult As Range) As String
Dim i As Integer
For i = 1 To rngTest.Cells.Count
If rngTest(i) = sCondition Then
cCat = cCat & rngResult(i) & ", "
End If
Next i
End Function