Excel 2010 - filter pivot table by pattern

0
votes

in Excel 2010 I am trying to analyze some data from an external analysis service.

In a pivot table I am trying to filter the report by one field which has multiple values separated by a comma. These look like this:

AB, CD1, EF1-5

AB, CD1,3, EF1

BCD, EFG

EXG, HIJ, CD1

...

So as you can see, there are hundreds of values in any possible order and no fixed scheme.

What I'm trying to achieve is to select all the fields which have a key beginning with an E (EF1-5, EF1, EFG, EXG, ...) and those beginning with H. These literals are never part of another key, so I could imagine using wildcards and creating a filter pattern like

*E* OR *H*

contains(E) || contains(H)

or equal. Is there any way to do this?

With best regards,

Babbage

edit: I've tried selecting some of the Keys manually by deselecting all and searching e.g. for EF1-5 to select them. But even then there are more than 10000 Keys with EF1-5 at different locations. So I can't even select them all. The plan was to create two or more pivot tables and merge the results.

1
excelexcel-2010ssaspivot-table

1 Answers

0
votes

Go to the drop down filter that you have in the pivot table, click on Select Multiple Items and deselect ALL. Then in the search bar (again at the pivot table filter) write *e* and select add current selection to filter. Repeat for *h*.

Do not forget to select add current selection to filter in both cases.