problem with #value!/zero

B

bill gras

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
 
G

Govind

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

bill gras

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
 
M

Max

bill gras said:
... Is there an other way with a formula
because the next day I have to do this all over again ..
Click to expand...

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

bill gras

Thanks Max
you are a genius and a gentelman!

Max said:
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 ..
Click to expand...
 
C

Curt

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
 
D

Dave Peterson

You have more followups at your original thread.
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
Click to expand...
 
B

Biff

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
 
Top