Advanced Filter

[anandmr65]

Hi
I have some 2000 rows and about 15 columns in a data sheet. I would
like to filter the data based on the conditions in 4 columns. the
conditions between columns is 'AND' and condition for rows is "OR".
Could anybody help me how to set the criteria range.

For ex:

Column A Column B Column C Column D
X A P I
Y B Q J
Z C R
D
E

The above show the different filter conditions I require. The filter
should satis fy Column A "And" Column B AND Column C AND Column D.
And for the rows it should filter on X or Y OR Z in column A , A OR B
OR C OR D OR E in column B and so on.

Can we put this table and selct the entire range for criteria range and
do a advanced filter?. It is not working. So I request you to help me on
this.

Thanks in advance for your help.

Regards
Anand

 

[Marcelo Rychter]

Hi Anand,

I think your need is not supported by Excel filters. Maybe the
solutions for your problem is a VBA code which perform the logic tests
and produce a new spreadsheet with the result or a list box, depending
on the number of rows selected.

Marcelo Rychter

 

[Ardus Petus]

Conditions are "AND'ed" by row.
So it's better to have only one row with formulae like:
=COUNTIF(myAbsoluteRange,A2)

See example: http://cjoint.com/?gfikjZVGkf

HTH
--
AP


"anandmr65" <[email protected]> a écrit
dans le message de (e-mail address removed)...

 

[anandmr65]

Hi Anand,

I think your need is not supported by Excel filters. Maybe the
solutions for your problem is a VBA code which perform the logic tests
and produce a new spreadsheet with the result or a list box, depending
on the number of rows selected.

Marcelo Rychter

Hi Marcelo Rychter,
Thanks for the rely
I feel that this could be solved by using Advanced filter, but since it
has more than one column I am having doubt as to how to set the
criteria..... Does anybody have an idea on resilving this through
Advanced filter rather than a VBA code.

Thanks & Regards
Anand

 

[anandmr65]

Hi Anand,

I think your need is not supported by Excel filters. Maybe the
solutions for your problem is a VBA code which perform the logic tests
and produce a new spreadsheet with the result or a list box, depending
on the number of rows selected.

Marcelo Rychter
Hi Marcelo Rychter,
Thanks for the rely
I feel that this could be solved by using Advanced filter, but since it
has more than one column I am having doubt as to how to set the
criteria..... Does anybody have an idea on resilving this through
Advanced filter rather than a VBA code.

Thanks & Regards
Anand

 

[anandmr65]

Hi AP,

Thanks a lot for solution.

But, I confess that I am unable to understand how will this wor
because the formula sees only the 2 nd row for counting (A2, B2 etc.)
It will be helpful if you could eductae me on how your solution works.
Sorry if I am sounding very stupid.

Thanks again for your help.

Regards
Anan