conditionally combining text value of multiple cells

[n.niks]

I have a table
A B C
1 completed 5
2 statement 0
3 call 3
4 w/off 1
5 resubmission 0
- -
- -
nth row

I want to make a formula in cell C1 that if value in column B is more than 0 then text of respectiv column A should be combined

output:-
completed, call, w/off

Note:- no of rows are not fixed

[NBVC]

First create a Dynamic Named Range so that you don't include more rows than necessary.... This will allow you to grow rows.

Go to Formula Tab and then Define Name.. enter a name like MyList and enter formula in Refers to field like: =Sheet1!$A1:INDEX(Sheet1!$B:$B,COUNTA(Sheet1!$A:$A)) assuming data is in column A:B of Sheet1.

then hold the Alt key and press F11 to get the VB Editor, go to Insert and select Module and paste this code in the Editor:

Please Login or Register  to view this content.

Close the editor.

In cell C1 enter formula:

=SUBSTITUTE(TRIM(aconcat(IF(INDEX(MyList,0,2)>0,INDEX(MyList,0,1),"")," "))," ",", ")

and hold the CTRL and SHIFT keys down and hit ENTER. You should see { } brackets appear around the formula and get your results.

Note that this method may be limited if you have excess number of rows....

[n.niks]

thanks a lot for replying, now I can solve my problem. This is best result.

thanks once again for help