problem with #value!/zero

[Guest]

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

 

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

 

[Guest]

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

 

[Max]

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

 

[Guest]

Thanks Max
you are a genius and a gentelman!

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

 

[Max]

You're welcome !
Thanks for the feedback ..

 

[Guest]

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

 

[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

 

[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

 

[Biff]

You have more followups at your original thread.

Where is that?

Nevermind, I found it!

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