Monday, March 5, 2012

yogi_Use Split With An Array Correctly Handling Items In The First Row As Well

Yogi Anand, D.Eng, P.E.                Google Spreadsheet          Post  #457          www.energyefficientbuild.com

user istril said:
using split with an array not working as expected, also won't sort
here is mydata:
libj:_old_do_NOT_      0   0   0   0   0   0   0    0
Libb:_Crown_Point     82  82   0  76   7  82   6 1266
Libc:_East_Chicag      0   0   0   0   0   0   0    0
Libd:_Gary             0   0   0   0   0   0   0    0
Libe:_Hammond          2   2   0   1 100   2   0  100
Libf:_Jasper_Coun    138 138   0 141  -3 138  11 1154
Libh:_Lake_County     11  11   0  11   0  11   0  100
Libg:_LaCrosse         2   2   0   2   0   2   0  100
Libk:_Lowell         118 120  -2 122  -4 118  10 1080
Libi:_LaPorte          2   2   0   2   0   2   0  100
Libl:_Michigan_Ci      1   1   0   1   0   1   0  100
Libm:_North_Judso      1   1   0   1   0   1   0  100
Liba:_Any_Other_L     11  11   0  14 -22  11   1 1000
Libn:_Pulaski_Cou      0   0   0   0   0   0   0    0
Libo:_Starke_Coun      2   3 -34   4 -50   2   0  100
Libq:_Westchester      3   3   0   2  50   3   0  100
Libs:_Whiting          1   1   0   1   0   1   0  100
Libp:_Wanatah          0   0   0   0   0   0   0    0
Libr:_Westville        2   2   0   2   0   2   0  100
when I enter the data, and then use
=split(A1," :")
and drag the formula down it works fine. (I am using both a space and a colon as delimiters)
BUT, this data is actually buried in a lot of other data, so I am calling it with a filter. When I use:
=arrayformula(split(filter("mydata")," :"))
it behaves oddly: for the first row of data, it will populate the first cell (the cell that actually contains the formula, so in this case it has "libj") but no other cells in that row. The data that would normally appear in that row is just gone. There also aren't any of the "--" in that row that you normally see when an arrayformula is continued. The rest of the range seems to populate as expected.
One other odd bit of behavior is that if I try and wrap this in a "sort" function, it doesn't sort at all. I have tried wrapping different chunks of the formula in sort and I can't get it to work anywhere.
anyone know why this isn't working?
------------------------------------------
there is a problem with the Google spreadsheet's SPLIT function not correctly handling the data in the first row of an array (as of this time) is a known problem ... the problem has been intimated to Google Docs Team

so I use a workaround by introducing a blank row about the first row of the real array as shown in my following solution to the problem