Conditional Autofilter For Formula

[Ms-Exl-Learner]

Hi All,

Whether any option is there in MS-XL-2003 or upto 2007 to get the value
using Autofilter for this below criteria:-

Assume that I am having values in A Column and the Example data is given below

A Column
Status (heading)
22
66
44
Apple
Banana
88
77
Mango
66
99

Now I just want to filter only Numbers. How I can do it? I know that I can
use any one of this functions in B2 Cell to find only the numbers
[=ISTEXT(A2), =T(A2) & =VALUE(A2)] and may be some other functions. But all
these functions should be entered in some other column and after that I have
to put filter to that particular column to get the results, But I don’t want
to add another column to do it. Just in Autofilter itself I want to do it.
Whether it’s possible???.

In the same case I am having another doubt, whether I can use FORMULA in
AUTOFILTER Like Conditional Formatting & Validation>>Custom. Because in both
Conditional Formatting & Validation>>Custom we can able to use formula.

Assume that I am having values in A column with duplicate values now I want
to see only the Original Value without duplication. The example is given
below:-

22
22
66
22
66
22
88
66
22
66
99

=IF(COUNTIF($A$1:$A1,$A1)>1,MATCH($A1,$A$1:$A1,0),"Original Values")

If I use this in next column then I can able to get Original Value. But all
this examples I want to use these functions in Autofilter itself to derive
the results. Whether this concept is already introduced in Excel?

If not whether it can be added in the next Excel Version as CONDITIONAL
AUTOFILTER?

 

[Pete_UK]

To see just the numbers in your first example, select a Custom filter
of Is Less Than, and then enter a large number like 999999.

I don't know why you want to avoid using a helper column - there are
limitations in what Excel can do, and once you have learnt those
limitations you can devise ways of overcoming them. The use of a
helper column (which is often just temporary) is a great way of
getting round the limitations. In your second example, a helper column
can tell you which cells are duplicate, so that you can apply
autofilter to those.

You might also care to learn about the capabilities of Advanced Filter
- Debra Dalgleish has some fine notes at:

http://www.contextures.com/xladvfilter01.html

Hope this helps.

Pete

 

[Ms-Exl-Learner]

Thanks for your guidance Mr. Peter. But, just I wanted to know whether it’s
possible to put formula in autofilter? I am not sure whether this option is
available in Excel or not? And I thought that I may not be aware of it. That
is the reason I have posted it to get knowledge from the experts like you.

Once again thanks for your reply…

--------------------
(MS-Exl-Learner)
--------------------

To see just the numbers in your first example, select a Custom filter
of Is Less Than, and then enter a large number like 999999.

I don't know why you want to avoid using a helper column - there are
limitations in what Excel can do, and once you have learnt those
limitations you can devise ways of overcoming them. The use of a
helper column (which is often just temporary) is a great way of
getting round the limitations. In your second example, a helper column
can tell you which cells are duplicate, so that you can apply
autofilter to those.

You might also care to learn about the capabilities of Advanced Filter
- Debra Dalgleish has some fine notes at:

http://www.contextures.com/xladvfilter01.html

Hope this helps.

Pete

Hi All,

Whether any option is there in MS-XL-2003 or upto 2007 to get the value
using Autofilter for this below criteria:-

Assume that I am having values in A Column and the Example data is given below

A Column
Status (heading)
22
66
44
Apple
Banana
88
77
Mango
66
99

Now I just want to filter only Numbers. How I can do it? I know that I can
use any one of this functions in B2 Cell to find only the numbers
[=ISTEXT(A2), =T(A2) & =VALUE(A2)] and may be some other functions. But all
these functions should be entered in some other column and after that I have
to put filter to that particular column to get the results, But I don’t want
to add another column to do it. Just in Autofilter itself I want to do it.
Whether it’s possible???.

In the same case I am having another doubt, whether I can use FORMULA in
AUTOFILTER Like Conditional Formatting & Validation>>Custom. Because in both
Conditional Formatting & Validation>>Custom we can able to use formula.

Assume that I am having values in A column with duplicate values now I want
to see only the Original Value without duplication. The example is given
below:-

22
22
66
22
66
22
88
66
22
66
99

=IF(COUNTIF($A$1:$A1,$A1)>1,MATCH($A1,$A$1:$A1,0),"Original Values")

If I use this in next column then I can able to get Original Value. But all
this examples I want to use these functions in Autofilter itself to derive
the results. Whether this concept is already introduced in Excel?

If not whether it can be added in the next Excel Version as CONDITIONAL
AUTOFILTER?

 

[Pete_UK]

You're welcome.

I'm not sure about the "experts" bit - I'm still learning !! <bg>

Pete

Thanks for your guidance Mr. Peter.  But, just I wanted to know whetherit’s
possible to put formula in autofilter? I am not sure whether this option is
available in Excel or not?  And I thought that I may not be aware of it.. That
is the reason I have posted it to get knowledge from the experts like you..

Once again thanks for your reply…

--------------------
(MS-Exl-Learner)
--------------------

To see just the numbers in your first example, select a Custom filter
of Is Less Than, and then enter a large number like 999999.

I don't know why you want to avoid using a helper column - there are
limitations in what Excel can do, and once you have learnt those
limitations you can devise ways of overcoming them. The use of a
helper column (which is often just temporary) is a great way of
getting round the limitations. In your second example, a helper column
can tell you which cells are duplicate, so that you can apply
autofilter to those.

You might also care to learn about the capabilities of Advanced Filter
- Debra Dalgleish has some fine notes at:

http://www.contextures.com/xladvfilter01.html

Hope this helps.

Pete

Hi All,
Whether any option is there in MS-XL-2003 or upto 2007 to get the value
using Autofilter for this below criteria:-
Assume that I am having values in A Column and the Example data is given below
A Column
Status (heading)
22
66
44
Apple
Banana
88
77
Mango
66
99
Now I just want to filter only Numbers.  How I can do it?  I knowthat I can
use any one of this functions in B2 Cell to find only the numbers
[=ISTEXT(A2), =T(A2) & =VALUE(A2)] and may be  some other functions.  But all
these functions should be entered in some other column and after thatI have
to put filter to that particular column to get the results, But I don’t want
to add another column to do it.  Just in Autofilter itself I want to do it.  
Whether it’s possible???.
In the same case I am having another doubt, whether I can use FORMULAin
AUTOFILTER Like Conditional Formatting & Validation>>Custom.  Because in both
Conditional Formatting & Validation>>Custom we can able to use formula.
Assume that I am having values in A column with duplicate values now I want
to see only the Original Value without duplication.  The example isgiven
below:-
22
22
66
22
66
22
88
66
22
66
99
=IF(COUNTIF($A$1:$A1,$A1)>1,MATCH($A1,$A$1:$A1,0),"Original Values")
If I use this in next column then I can able to get Original Value.  But all
this examples I want to use these functions in Autofilter itself to derive
the results.  Whether this concept is already introduced in Excel?  
If not whether it can be added in the next Excel Version as CONDITIONAL
AUTOFILTER?

- Show quoted text -

 

[Joachim]

Hallo MS-Exl-Learner

You cannot use formulas in autofilter. As Pete pointed out to use formulas
for filtering you need to use advanced filter.
For filtering out duplicates you check no duplicates in the advanced filter
dialogue box.