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 news:
[email protected]...
>
> 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
>
>
> --
> anandmr65
> ------------------------------------------------------------------------
> anandmr65's Profile:
> http://www.excelforum.com/member.php...o&userid=30728
> View this thread: http://www.excelforum.com/showthread...hreadid=548449
>

[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 work 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
Anand