Advanced Filter

[Guest]

I am using the advanced filter to extract information from my spreadsheet. I
setup the filter in the following way

PI Name Name Role Country
Principal Brazil
Sub Mexico
Specialist Argentina

When it filters it only gives me two of the criteria. It will give me
Prinicpal and sub in Brazil and Mexico but not specialist and Argentina. What
I want is all principal, sub and specialists in Brazil, Mexico and Argentina.
Have I entered the criteria wrong? If so how would I enter the criteria to
get the results I am looking for.

Thanks in advance for your help.

Regards,

Dee

 

[Bob Phillips]

I think you have to specify the combinations

Role Country
Principal Brazil
Sub Mexico
Specialist Argentina
Principal Mexico
Sub Argentina
Specialist Brazil
Principal Argentina
Sub Brazil
Specialist Mexico




--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Guest]

Hi Dee- Check out Advanced Filter. For Excel 2002, this appears to allow
more than 2 values for each column of data.

Or, a quick way would be to:

Copy the Country column to new column CountryCopy. Replace Brazil, Mexico,
Argentina in CountryCopy with ZZBrazil, ZZMexico, ZZArgentina. Data, Filter
column
Role with "begins with" "s*". Data Filter column Country Copy with
"begins with "zz*".

PB

 

[Debra Dalgleish]

Enter the Role and Country lists on the worksheet (not in the criteria area)
In the criteria area, leave the heading cell blank.
In the cell below, enter a formula that refers to the Role and Country
lists, e.g.:

=AND(COUNTIF($K$2:$K$4,C2),COUNTIF($L$2:$L$4,D2))

where C2 contains the first Role in the table to be filtered, and D2
contains the first Country.

When you run the Advanced Filter, select the blank heading cell and the
cell with the formula as the Criteria range.