yogi_Pull Data For Specified Units And Concatenate For Each Unit Representation

                                          Google Spreadsheet   Post  #1302
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul 23, 2013
user Justin Duffy (http://productforums.google.com/forum/?zx=ry5tku6r6dx3#!mydiscussions/docs/-3iUFFZDSnU)
QUERY

I want to check all the cells in a sheet named 'Process' for the word 'UNIT'.

I've got this formula which seems to do the trick:

=QUERY('Process'!A:A;"select * where A contains 'UNIT'")

My question is this: How can I adjust this to return the cells immediately BELOW the values that this would return. Ideally, I'd like something that concatenates all the cells below the one containing the text 'UNIT' until it gets to the next cell that says 'UNIT'. Even if I could just adjust this to return the position of the cell, maybe I could work from there.

I am trying to create a sheet that imports data from another tool and hoping I can get it to auto-sort through the digital muck.

thanks!

---

Hi yogi!

Thanks for your response.  This might be a little clearer:

I am trying to create a formula that will turn the 'process' sheet of this spreadsheet into the 'formatted'.  This is just an example, but the tricky part is that there will many (50+) sheets imported and copied to the 'process' sheet (via a script, which will then process them and cut them out), and the UNIT title cells may be in different rows.  Also, the data below the UNIT cells may be in one or more cells and would need to be concatenated on the 'formatted' page.  I've made some headway with QUERY, using the formula: 

=QUERY('Process'!A:A;"select * where A contains 'UNIT'")

but I'm not sure how to get the stuff below.  Any ideas/help would be greatly appreciated.  The sheet below is my example, and you can see there are no formulas on the 'formatted' page.  Feel free to take a crack at it if you have any ideas.

Sample Spreadsheet

thanks!

----------------------------------------------------------------------------------------------------------------------------------------------------