Sunday, May 27, 2012

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

user TomSleekUK said:
Simplify this long formula?
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