|
problem with #value!/zero
how can I delete cells that have a formula and that formula equals 0 (zero)
in one column and #value! in a other column? eg:- a b 1 0 #value! 2 234.3 3.78 3 0 #value! 4 327 4.712 5 54 23 6 0 #value! 7 0 #value! 8 156 89.7 and so on down to 300 rows . the zero and value cells are not always in the same order they are in random order can some one please help? thanks bill |
Hi,
Use Filter-Auto filter and select those rows (usign the condition that value is zero in first column and error message in second column) and then delete those rows. Govind. bill gras wrote: how can I delete cells that have a formula and that formula equals 0 (zero) in one column and #value! in a other column? eg:- a b 1 0 #value! 2 234.3 3.78 3 0 #value! 4 327 4.712 5 54 23 6 0 #value! 7 0 #value! 8 156 89.7 and so on down to 300 rows . the zero and value cells are not always in the same order they are in random order can some one please help? thanks bill |
Hi Govind
Thank you for your reply Is there an other way with a formula because the next day I have to do this all over again thanks "Govind" wrote: Hi, Use Filter-Auto filter and select those rows (usign the condition that value is zero in first column and error message in second column) and then delete those rows. Govind. bill gras wrote: how can I delete cells that have a formula and that formula equals 0 (zero) in one column and #value! in a other column? eg:- a b 1 0 #value! 2 234.3 3.78 3 0 #value! 4 327 4.712 5 54 23 6 0 #value! 7 0 #value! 8 156 89.7 and so on down to 300 rows . the zero and value cells are not always in the same order they are in random order can some one please help? thanks bill |
"bill gras" wrote
... Is there an other way with a formula because the next day I have to do this all over again .. One non-array formulas play to make it dynamic .. Assume data is in Sheet1, cols A & B, data from row1 down Using an empty col to the right, say col D, Put in D1: =IF(COUNTBLANK(A1:B1)=2,"",IF(AND(A1=0,ISERROR(B1) ),"",ROW())) Copy D1 down to say, D500, to cover the max expected data in cols A & B (can copy down ahead of data input) In Sheet2 -------- Put in A1: =IF(ISERROR(SMALL(Sheet1!$D:$D,ROWS($A$1:A1))),"", INDEX(Sheet1!A:A,MATCH(SMA LL(Sheet1!$D:$D,ROWS($A$1:A1)),Sheet1!$D:$D,0))) Copy A1 across to B1, fill down to B500 (cover the same range as done in col D in Sheet1) Sheet2 will return only the desired rows from Sheet1's cols A & B, all neatly bunched at the top. For the sample data posted, you'd get: 234.3 3.78 327 4.712 54 23 156 89.7 (blank rows below) Adapt to suit .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
Thanks Max
you are a genius and a gentelman! "Max" wrote: "bill gras" wrote ... Is there an other way with a formula because the next day I have to do this all over again .. One non-array formulas play to make it dynamic .. Assume data is in Sheet1, cols A & B, data from row1 down Using an empty col to the right, say col D, Put in D1: =IF(COUNTBLANK(A1:B1)=2,"",IF(AND(A1=0,ISERROR(B1) ),"",ROW())) Copy D1 down to say, D500, to cover the max expected data in cols A & B (can copy down ahead of data input) In Sheet2 -------- Put in A1: =IF(ISERROR(SMALL(Sheet1!$D:$D,ROWS($A$1:A1))),"", INDEX(Sheet1!A:A,MATCH(SMA LL(Sheet1!$D:$D,ROWS($A$1:A1)),Sheet1!$D:$D,0))) Copy A1 across to B1, fill down to B500 (cover the same range as done in col D in Sheet1) Sheet2 will return only the desired rows from Sheet1's cols A & B, all neatly bunched at the top. For the sample data posted, you'd get: 234.3 3.78 327 4.712 54 23 156 89.7 (blank rows below) Adapt to suit .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
You're welcome !
Thanks for the feedback .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "bill gras" wrote in message ... Thanks Max you are a genius and a gentelman! |
problem with #value!/zero
Is there a way to get excel 2000 to input zero's into blank spaces with a
macro? useing edit & got to etc don't work curt "Max" wrote: You're welcome ! Thanks for the feedback .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "bill gras" wrote in message ... Thanks Max you are a genius and a gentelman! |
problem with #value!/zero
You have more followups at your original thread.
Curt wrote: Is there a way to get excel 2000 to input zero's into blank spaces with a macro? useing edit & got to etc don't work curt "Max" wrote: You're welcome ! Thanks for the feedback .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "bill gras" wrote in message ... Thanks Max you are a genius and a gentelman! -- Dave Peterson |
problem with #value!/zero
You have more followups at your original thread.
Where is that? I'm actually trying my hand at some VBA and would like to see how you pros code it! Biff "Dave Peterson" wrote in message ... You have more followups at your original thread. Curt wrote: Is there a way to get excel 2000 to input zero's into blank spaces with a macro? useing edit & got to etc don't work curt "Max" wrote: You're welcome ! Thanks for the feedback .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "bill gras" wrote in message ... Thanks Max you are a genius and a gentelman! -- Dave Peterson |
problem with #value!/zero
You have more followups at your original thread.
Where is that? Nevermind, I found it! Biff "Biff" wrote in message ... You have more followups at your original thread. Where is that? I'm actually trying my hand at some VBA and would like to see how you pros code it! Biff "Dave Peterson" wrote in message ... You have more followups at your original thread. Curt wrote: Is there a way to get excel 2000 to input zero's into blank spaces with a macro? useing edit & got to etc don't work curt "Max" wrote: You're welcome ! Thanks for the feedback .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "bill gras" wrote in message ... Thanks Max you are a genius and a gentelman! -- Dave Peterson |
| All times are GMT +1. The time now is 06:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com