Excel Pivot Table Filter : Items Begining with "A" or "W"?

Question

Is it possible to have a multiple search criteria in Excel?

Is there any way to use logical constructs like OR etc. in filter criteria of a pivot table?

Update : In the filter options ( when one click the triangle in the caption of the field) the filter for "Begins With:" can only be used to filter only on one set of characters, e.g. find all the items that begin with "ABC" only, but I want to filter the items that beging with "AB" or CD" only.

In Sql this is a no brainer with the "like 'AB%' or like 'CD%'" , but I want to do the filtering in the pivot table.

Solution 1

You could use the MID function and then do a string comparison with the result.

=MID("apple",2,3)  //returns "ppl".

I tested the next formula in Google Spreadsheets. I think Excel can do something similar to this:

=OR(EQ(MID(A1,1,2),"AB"),EQ(MID(A1,1,2),"CD"))

Solution 2

Try filtering on at least one blank row above Pivot Table as well as Pivot Table.

Comments

[Nour Kembal]

@Raystafarian : In the Pivot table, With any text field, when trying to filter there is only one way of filtering "Begins with" and there is no wat to say filter Items begining with "K" and "C" only.

[Raystafarian]

Use filters instead of a pivot table

[Nour Kembal]

@Raystafarian : Ray, I have the data in the Pivot table, That is how they are eant to be summurized. What do you mean to use Filters instead of PT? I just want to get the results that match a criteria in the Pivot Table.