Wednesday, May 23, 2012

yogi_Concatenate Data From Various Columns Row By Row Per Specification

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

use tabus said:
arrayformula causing trouble 
Hi there,
I've got this problem:
The spreadsheet contains 4 columns where dates (formatted as text, but it must be so) are inserted and the 5th column, where as a result I have the input from columns 1-4, joined together but separated with ";". So it looks like this:
column A       column B     column C     column D                   column E (result)
2012.02.01   2012.02.03   2012.03.01   2012.03.12   2012.02.01;2012.02.03;2012.03.01;2012.03.12
However, the condition is that each of the dates in the result column can be followed by the separator ";" only, when following cell is neither empty nor filled with "nd" text. 
In other words the condition is met only when following cell (within 1-4 column) is filled with the date. So, if only the columns A-B were properly completed, the table would look like this:
column A       column B     column C     column D                   column E (result)
2012.02.01   2012.02.03                                                  2012.02.01;2012.02.03
or:
column A       column B     column C     column D                   column E (result)
2012.02.01         nd                nd               nd                       2012.02.01
Now, the formula I used is as follows:
=if(and(A2<>"";B2<>"nd";B2<>"");A2&";";if(A2<>"";A2;""))&if(and(B2<>"";C2<>"nd";C2<>"");B2&";";if(and(B2<>"";B2<>"nd");B2;""))&if(and(C2<>"";D2<>"nd";D2<>"");C2&";";if(and(C2<>"";C2<>"nd");C2;""))&if(D2<>"nd";D2;"") 
and it works fine, UNTIL "ARRAYFORMULA" IS USED AT THE BEGINNING
=arrayformula(if(and(A2:A<>"";B2:B<>"nd";B2:B<>"");A2:A&";";if(A2:A<>"";A2:A;""))&if(and(B2:B<>"";C2:C<>"nd";C2:C<>"");B2:B&";";if(and(B2:B<>"";B2:B<>"nd");B2:B;""))&if(and(C2:C<>"";D2:D<>"nd";D2:D<>"");C2:C&";";if(and(C2:C<>"";C2:C<>"nd");C2:C;""))&if(D2:D<>"nd";D2:D;""))
Then the table looks like this:
column A       column B     column C     column D                   column E (result)
2012.02.01   2012.02.03   2012.03.01   2012.03.12              2012.02.012012.03.12
So, columns A and D are joined together but without the separator, and columns B-C vanish entirely!
Any ideas what I'm doing wrong and how to fix it?
Thanks in advance:)
Browser - Chrome
OS - Win7 HP x64
-------------------------------------------------------------------------------------------------
following is a solution to the problem