Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 ======================= |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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,)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
combining columns and using concatenate | Excel Worksheet Functions | |||
CONCATENATE TWO COLUMNS | Excel Worksheet Functions | |||
Concatenate multiple rows and columns into 1 cell | Excel Discussion (Misc queries) | |||
Concatenate Four Columns With Seperators | Excel Discussion (Misc queries) | |||
Concatenate two columns? | Excel Worksheet Functions |