Advanced Filter
 

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

Advanced Filter
 

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


--
Marcelo Rychter
------------------------------------------------------------------------
Marcelo Rychter's Profile: http://www.excelforum.com/member.php...o&userid=35066
View this thread: http://www.excelforum.com/showthread...hreadid=548449

Advanced Filter
 

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" a écrit
dans le message de news:
...

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

Advanced Filter
 

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

Advanced Filter
 

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

Advanced Filter
 

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


--
anandmr65
------------------------------------------------------------------------
anandmr65's Profile: http://www.excelforum.com/member.php...o&userid=30728
View this thread: http://www.excelforum.com/showthread...hreadid=548449