Concatenate for multiple columns
 

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.

Concatenate for multiple columns
 

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
=======================

Concatenate for multiple columns
 

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"

One way is to build the string sequentially.

Using row 4 for a result, I started at a column to the right of the
longest possible input row. Suppose that's column P.

In P4, I put
=TRIM(IF(N(P2)=0,"",P1&" ")&IF(Q4="","",Q4))
and extended it leftward to column A. The desired result should be in
A4.

Finally, I changed the font color of B4:P4 to white, so the
intermediate results don't show.

Concatenate for multiple columns
 

On May 23, 6:33*am, 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"


Something like this might also work for you.
=CONCATENATE(IF(A3=1,$A$2,),IF(B3=1,$B$2,),IF(C3=1 ,$C$2,),IF(D3=1,$D
$2,),IF(E3=1,$E$2,),IF(F3=1,$F$2,),IF(G3=1,$G$2,), IF(H3=1,$H$2,))