Monday, April 1, 2013

yogi_Pull Data From A Table Avoiding Gaps Using FILTER Or QUERY Function


                                          Google Spreadsheet   Post  #1091
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr 1, 2013
user RKL1 :(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/2J7cZmQkGs8)
I am trying to query data that has a lot of empty cells
when I use the query function
=TRANSPOSE(query(B2:M21,"select C,D,E,F,G,H,I,L where B ="&(E30)&" "))
I am left with empty rows in the output. Is there a way avoid empty rows and have the data output compacted?
I've tried
=TRANSPOSE(Filter(C2:M20,B2:B20=E31))
and the result is the same.
This is my spreadsheet. The two equations are under cells E32 and E50.
https://docs.google.com/spreadsheet/ccc?key=0Aq6cJKwZxwyOdHFwTDc2SS03YkdVem5EM3lhMS0tQnc#gid=0
Thank you
RKL1

-----------------------------------------------------------------------------------------
following is a solution to the problem