deleting vs. filtering . . . HELP!

W

Wayne Knazek

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.

:)
 
G

Glen

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
 
M

Max

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)
Click to expand...

Another tinker to play with ..

Try in say D1:
=IF(TRIM(C1)="","",IF(ISTEXT(C1),"no",IF(C1+0<=0,"no","yes")))
Copy down
 
W

Wayne Knazek

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 :)
 
M

Max

Wayne Knazek said:
OK, that worked great!
Click to expand...

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.
Click to expand...
....

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

Wayne Knazek

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

Max

Thanks for replying
... was . . IF(AND(T(C1)<>C1,C1>0),"yes","no")
Click to expand...
... 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.
 
Top