deleting vs. filtering . . . HELP!
 

In a nutshell . . . A TEXT FILE IS IMPORTED . . .

In column C (example) there will either be text, a numeric value, or a
"zero" (0).

I need to set up a filter so that only rows with a numeric values 0 show.
(No rows with text or a zero in C will show)

I tried to set up a helper column that (example) would have a "yes" in
column D if the cell (C1) had a plus value, and "no" if it had text or a
zero.

I used . . . =IF(C1<=0,"no","yes") Works OK to eliminate cells with a zero
or negative number. There's a "no" in the next column.

I figuresd I'd get a "yes" in the columns with only text.

BUT NO! If there's text in the column, other than the number 0, it counts
as a numeric value. So I get a "yes" in any row with text or a value greater
than zero.

0 enters a no (good!)
a negative number enters a no (Yazza!)
a positive number enters a yes (poifec!)
any text enters a yes (NO!)

Hmmm. Comments welcome.

:)

deleting vs. filtering . . . HELP!
 

Try
=IF(AND(T(C4)<C4,C40),"yes","no")

the two conditions check that the cell has a value that is greater than
zero, and that the cell contents aren't text.... the T() function returns
the value of the cell only if the contents are text.

Cheers,

Glen

deleting vs. filtering . . . HELP!
 

I need to set up a filter so that only rows with a numeric values 0 show.
(No rows with text or a zero in C will show)

Another tinker to play with ..

Try in say D1:
=IF(TRIM(C1)="","",IF(ISTEXT(C1),"no",IF(C1+0<=0," no","yes")))
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Wayne Knazek" wrote:
In a nutshell . . . A TEXT FILE IS IMPORTED . . .

In column C (example) there will either be text, a numeric value, or a
"zero" (0).

I need to set up a filter so that only rows with a numeric values 0 show.
(No rows with text or a zero in C will show)

I tried to set up a helper column that (example) would have a "yes" in
column D if the cell (C1) had a plus value, and "no" if it had text or a
zero.

I used . . . =IF(C1<=0,"no","yes") Works OK to eliminate cells with a zero
or negative number. There's a "no" in the next column.

I figuresd I'd get a "yes" in the columns with only text.

BUT NO! If there's text in the column, other than the number 0, it counts
as a numeric value. So I get a "yes" in any row with text or a value greater
than zero.

0 enters a no (good!)
a negative number enters a no (Yazza!)
a positive number enters a yes (poifec!)
any text enters a yes (NO!)

Hmmm. Comments welcome.

deleting vs. filtering . . . HELP!
 

Yazza! Thanks! Will try both ideas today.

Much appreciated!

Wayne

"Max" wrote:

I need to set up a filter so that only rows with a numeric values 0 show.
(No rows with text or a zero in C will show)

Another tinker to play with ..

Try in say D1:
=IF(TRIM(C1)="","",IF(ISTEXT(C1),"no",IF(C1+0<=0," no","yes")))
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Wayne Knazek" wrote:
In a nutshell . . . A TEXT FILE IS IMPORTED . . .

In column C (example) there will either be text, a numeric value, or a
"zero" (0).

I need to set up a filter so that only rows with a numeric values 0 show.
(No rows with text or a zero in C will show)

I tried to set up a helper column that (example) would have a "yes" in
column D if the cell (C1) had a plus value, and "no" if it had text or a
zero.

I used . . . =IF(C1<=0,"no","yes") Works OK to eliminate cells with a zero
or negative number. There's a "no" in the next column.

I figuresd I'd get a "yes" in the columns with only text.

BUT NO! If there's text in the column, other than the number 0, it counts
as a numeric value. So I get a "yes" in any row with text or a value greater
than zero.

0 enters a no (good!)
a negative number enters a no (Yazza!)
a positive number enters a yes (poifec!)
any text enters a yes (NO!)

Hmmm. Comments welcome.

deleting vs. filtering . . . HELP!
 

OK, that worked great! Buit now I have another issue on the same sheet.

There are rows I don't want to show, even if they contain a positive value
in Column C. The request wasn't made clear to me. Now I need to redo this a
little.

The good news is, the data always comes in, in the same format. So the data
in the rows I want to filter out is always the same.

Your formula ( =IF(A22="Y",C22*(-1)) ) worked fine. When I run it, I end up
with 6 rows of data. All the other "stuff" is gone. :)

Here's what I have, and what I need . . .

Now (after filter ran)

A B C
TE | |0.055
LE | |0.094
X | |.559
Y | |0.749
Z | |0.987
L | |0.296

What I need to do is show only this . . . (No X, Y opr Z)

A B C
TE | |0.055
LE | |0.094
L | |0.296

Hmmmmmm :)











"Wayne Knazek" wrote:

In a nutshell . . . A TEXT FILE IS IMPORTED . . .

In column C (example) there will either be text, a numeric value, or a
"zero" (0).

I need to set up a filter so that only rows with a numeric values 0 show.
(No rows with text or a zero in C will show)

I tried to set up a helper column that (example) would have a "yes" in
column D if the cell (C1) had a plus value, and "no" if it had text or a
zero.

I used . . . =IF(C1<=0,"no","yes") Works OK to eliminate cells with a zero
or negative number. There's a "no" in the next column.

I figuresd I'd get a "yes" in the columns with only text.

BUT NO! If there's text in the column, other than the number 0, it counts
as a numeric value. So I get a "yes" in any row with text or a value greater
than zero.

0 enters a no (good!)
a negative number enters a no (Yazza!)
a positive number enters a yes (poifec!)
any text enters a yes (NO!)

Hmmm. Comments welcome.

:)

deleting vs. filtering . . . HELP!
 

You're welcome, Wayne.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Wayne Knazek" wrote:
Yazza! Thanks! Will try both ideas today.
Much appreciated!

Wayne

deleting vs. filtering . . . HELP!
 

"Wayne Knazek" wrote:
OK, that worked great!

Which suggestion/s worked for you?
Both?, Glen's or mine?

.. But now I have another issue on the same sheet.
.....
Your formula ( =IF(A22="Y",C22*(-1)) ) worked fine.
....

Neither Glen nor me gave you that suggestion. Perhaps you mixed it up with
another post/thread of yours. Anyway, whatever it is I would suggest that you
put in your new query as a new post. Since one of our suggestions worked for
your orig. post here, let's close this thread and move on ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

deleting vs. filtering . . . HELP!
 

My bad! I copied the wrong formula in my last post. The formula that worked
was . . .

The forumula in my last post was one from another cell,m resolving a
different problem.

deleting vs. filtering . . . HELP!
 

UGGHHHHHHHHHHHHH!

.... was . . IF(AND(T(C1)<C1,C10),"yes","no")

"Wayne Knazek" wrote:

My bad! I copied the wrong formula in my last post. The formula that worked
was . . .

The forumula in my last post was one from another cell,m resolving a
different problem.

deleting vs. filtering . . . HELP!
 

Thanks for replying

... was . . IF(AND(T(C1)<C1,C10),"yes","no")
... will leave it to Glen to follow through further with you

Btw, just curious why my suggestion didn't work (it was lightly tested ok
here). Which values in col C over at your end failed to return the desired
outcome when you tried it? Could you paste some of these in reply here?
Thanks.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---