View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.misc
zvkmpw zvkmpw is offline
external usenet poster
 
Posts: 153
Default 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.