yogi_Join Non-Blank Entries In A Given Range Of Columns And Transpose To Corresponding Rows Per Specification

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #566    May 27, 2012     www.energyefficientbuild.com.

user TomSleekUK said:
Simplify this long formula?

https://docs.google.com/spreadsheet/ccc?key=0AsJ9Fp_9-2PadGl3R2QtSzkwUjlNTi1XNS1sbVhHMEE

C17:U34 - Is where the data is

V5:V11 - Is where I want the result

Currently I am using a really long formula that leaves trailing commas, but thats not really important.

If C17>0 then join C17 to U17, if not then do nothing.

Then do the same to the next row (C18) and join it on to the first result

In this case U17 and U18 are 0 so they should be ignored

I want a formula that does this for each row and joins the results together e.g.

1 Tuna, 1 Brown rice, 1 Banana

If U17 was 1 then it would be

1 Chicken, 1 Tuna, 1 Brown rice, 1 Banana

It would be easier to work on the sheet together so I can point and live explain what I mean; it's hard doing this not in real time.

Thanks in advance

---------------------------------------------------------------
following is a solution ... in regard to the needed formula in cells V5:V11