On Mon, 23 May 2011 03:33:40 -0700 (PDT), richard Felix wrote:

1st Row : Anand ](Cell1) is(Cell2) a(Cell3) good boy. He Smokes Often.
2nd Row: 1 1 0 1
1 0 1 1
3rd Row : 1 1 1 1
1 1 1 0

The each word in the sentence is split for each cells. Below each cell
there is boolean values ( 1's and 0's).

Problem : I want to concatenate the words(Row1) for words having
values 1. then the output should be.


2nd Row Output: "Anand is good boy smokes often"
3rd Row Output: " Anand is a good boy he smokes"

1. if multicat how to customize the function


Thanks in advance.

Regards
Richard.

As far as I know, CONCATENATE will not work on arrays.

So a formula solution would be something like:

=TRIM(IF(A2=1,$A$1& " ","")&IF(B2=1,$B$1&" ","")&IF(C2=1,$C$1&" ","") & ... )

... = extend the same pattern for as many columns as necessary.

Or you could use a user defined function

To enter this User Defined Function (UDF), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=ConcatIF($A$1:$H$1,A2:H2)

in some cell.

==============================
Option Explicit
Function ConcatIF(rg As Range, rCriteria As Range, _
Optional Separator As String = " ") As Variant
Dim c As Range
Dim temp() As String
Dim i As Long, j As Long

If rg.Count < rCriteria.Count Then
ConcatIF = CVErr(xlErrValue)
Exit Function
End If

ReDim temp(1 To WorksheetFunction.CountIf(rCriteria, 1))
i = 1
For j = 1 To rCriteria.Count
If rCriteria(j) = 1 Then
temp(i) = rg(j)
i = i + 1
End If
Next j

ConcatIF = Join(temp, Separator)
End Function
=======================