Monday, March 19, 2012

yogi_Merge Data From Several Sheets And Extract Only Non-Blank Rows

Yogi Anand, D.Eng, P.E.                Google Spreadsheet          Post  #473         www.energyefficientbuild.com
user WEEN said:
Dear googlers duplicate a spreadsheet but formula can work ? I ve duplicate a complete sheet and i have rename just the title of the spreadsheet It appears that in one sheet i have the following formula that used to work in the original document but not in the copied one , how is it POSSIBLE ? =query(vmerge(SH1!A10:CH;SH2!A10:CH;SH3!A10:CH;'RERANGE SH4'!A10:CH),"select* where Col1<>'' or Col2<>'' or Col3<>'' or Col4<>'' or Col5<>'' or Col6<>'' or Col7<>'' or Col8<>'' or Col9<>'' or Col10<>'' or Col11<>'' or Col12<>'' or Col13<>'' or Col14<>'' or Col15<>'' or Col16<>'' or Col17<>'' or Col18<>'' or Col19<>'' or Col20<>'' or Col21<>'' or Col22<>'' or Col23<>'' or Col24<>'' or Col25<>'' or Col26<>'' or Col27<>'' or Col28<>'' or Col29<>'' or Col30<>'' or Col31<>'' or Col32<>'' or Col33<>'' or Col34<>'' or Col35<>'' or Col36<>'' or Col37<>'' or Col38<>'' or Col39<>'' or Col40<>'' or Col41<>'' or Col42<>'' or Col43<>'' or Col44<>'' or Col45<>'' or Col46<>'' or Col47<>'' or Col48<>'' or Col49<>'' or Col50<>'' or Col51<>'' or Col52<>'' or Col53<>'' or Col54<>'' or Col55<>'' or Col56<>'' or Col57<>'' or Col58<>'' or Col59<>'' or Col60<>'' or Col61<>'' or Col62<>'' or Col63<>'' or Col64<>'' or Col65<>'' or Col66<>'' or Col67<>'' or Col68<>'' or Col69<>'' or Col70<>'' or Col71<>'' or Col72<>'' or Col73<>'' or Col74<>'' or Col75<>'' or Col76<>'' or Col77<>'' or Col78<>'' or Col79<>'' or Col80<>'' or Col81<>'' or Col82<>'' or Col83<>'' or Col84<>'' or Col85<>'' or Col86<>'' ") please explain me why thi doesn't work, here here a copy of the page you can try to see my formula is in the test sheet https://docs.google.com/spreadsheet/ccc?key=0Atv06cGw9P0fdEc5cS14NmpPU1dZYm9oUjE3dHVrYWc 
regards
------------------------------------------------------------
WEEN's formula as shown in sheet Test seems to be OK ... but it is way too long and repeating with OR for 86 columns one at a time sequentially ... WOW!
In the following solution as presented in sheet Yogi_Test I took a little different approach and I came with a relatively much shorter formula ... so here we go