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.

Try
=IF(AND(T(C4)<>C4,C4>0),"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

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

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.

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

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

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

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.

UGGHHHHHHHHHHHHH!

.... was . . IF(AND(T(C1)<>C1,C1>0),"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.
>
>

Thanks for replying

> ... was . . IF(AND(T(C1)<>C1,C1>0),"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
---

Thanks for replying

> ... was . . IF(AND(T(C1)<>C1,C1>0),"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
---