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
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
Yogia you're really incredible, you're right my formula was crazy and not logical face to yours. I just love it;)
ReplyDeleteCongratulations
Ween
but i can't copy your formula :( could you put in on the online google document ?)
ReplyDeleteWhere did you learn all this ? Is there a tutoral to understand how to formulate this ?
Many regards,
Ween
i've this
ReplyDeleteiferror(if(column(A:CH),query(index(if(split(concatenate(column(A:CH)&char(9)),vmerge(SH!A10 :CH ;SH2!A10 :CH ;SH3!A10 :CH ;’RERANGE SH4’!A10 :CH),mmult(if(column(A:CH),len(vmerge(SH1!A10:CH;SH2!A10:CH;SH3!A10:CH;’RERANGE SH4!A10:CH))),transpose(column(A:CH)^0)))),’’select* where Col87>0’’)))
but maybe i copied badly the formula i've a macbook that's the problem with some symbol when i copy them
Hi ween:
ReplyDeleteI have shared the following spreadsheet
yogi_Merge Data From Several Sheets And Extract Only Non-Blank Rows
https://docs.google.com/spreadsheet/ccc?key=0AkHBcyclu11AdHZHelJ1bURReVJMUGFqSGQ1Y2VESlE#gid=22
with you for VIEWing -- the formula of interest is in sheet yogi_Test.
I hope this helps.
Cheers!
Yogi