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
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 
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