Conditional Concatenation

[u0107]

Hello,

I have a spreadsheet in which I have values from Column D onwards to the right. These values are texts. But since the values are populated on the basis of an array based match formula, I am unable to prevent Zero being put in the cells where the array formula does not find a match for a lookup value. Also the number of Columns in which the texts can exist differ for different rows.

Example:

D3="Sam"
E3="David"
F3="John"

D4="Jack"
E4="Victor"
F4="Mike"
G4="Juan"

and so on..

I would like to concatenate all non-blank values in Row 3 (Column D to ???) separated by (a comma and a space) and put it in the corresponding Column C of the same row.

In the example above C3 should contain "Sam, David, John" and C4 should contain "Jack, Victor, Mike, Juan"

Any suggestions please?

Thanks in advance.

Uttam

 

[Aladin Akyurek]

=MCONCAT(Range,"")

Requires the morefunc.xll add-in.