concatenate only non zero values

[NapervilleMike]

So here is what I have:

Substance	quantity
Water	98
Sodium	.5
Potassium	1
Calcium	.5
Magnesium	0

<tbody>
</tbody>

What I need is a formula that will concatenate only the non-zero's and the substance associated with them. For example this out put should say: Water, 98; Sodium .5; Potassium, 1; Calcium .5

If this seems pointless, the reason I need a methodology because I actually have about 50 substances and there are about 30 "quantity" columns and I would like a nice way to automate this and apply it to other similar applications.

Thanks.

 

[shashidharga]

Sir, You are a GENIUS .... Thanks a lot !!! It works !!!
Sir, One more thing, how to copy data in the multiple cells in a row to multiple cells in a column (like vallues from E10:H10 to D20:D23 ie., from cells on the rows to cells on the column)
Regards,
shashi

 

[Rick Rothstein]

Sir, One more thing, how to copy data in the multiple cells in a row to multiple cells in a column (like vallues from E10:H10 to D20:D23 ie., from cells on the rows to cells on the column)

You can do that manually quite easily. Select the cells you want to transpose and press CTRL+C to copy them to the clipboard, then select the starting cell for the new location and right-click it, select PasteSpecial from the popup menu that appears and then select Transpose on the dialog box that appears... finish by clicking the OK button.

 

[shashidharga]

Sir, I've changed/added some details in my excel file and emailed the same to your email address. Please suggest any easy method.
Shashi