Filtering pivot table according to cell values (VBA)

[hanmas]

Hi guys,
I feel I am missing something glaringly obvious here, but how can I use VBA to automatically filter a pivot table based on text contained within values? For example, I would like use a macro to automatically filter the following dataset to contain only rows where Product *contains* either "IX", "MM" or "NX":

Product ValueA ValueB
VD:IX:L:MM: Text Text 623 187
VD:MM: Text Text 390 80
VD:IX: Text Text 1100 430
VD:NX: Text Text 1235 685
VD:SP:L:FV: Text Text 96 63
VD:KP:L:FV: Text Text 0 0

Note that in reality, the pivot has 20,000 rows and I will need to filter for maybe thirty different values within the product names...
Thanks very much for your help,
Graham

 

[hanmas]

With a formatted table this time

Product	                  ValueA  ValueB
VD:IX:L:MM: Text Text	    623	   187
VD:MM: Text Text	    390	    80
VD:IX: Text Text	   1100	   430
VD:NX: Text Text	   1235	   685
VD:SP:L:FV: Text Text	     96	    63
VD:KP:L:FV: Text Text	      0      0

 

[hanmas]

Anyone able to help with this one please? I'm really struggling here...there must be a way to automatically filter a pivot table according to a defined set of text values by matching text within strings rather than matching the full string?
Thanks in advance for your help,
Graham

 

[circledchicken]

Hi,

Perhaps add a column alongside your dataset with the formula:

=SUMPRODUCT(--ISNUMBER(SEARCH({"IX","MM","NX"},A2)))>0

Add this column to your PivotTable and filter for TRUE.

 

[hanmas]

Thanks very much for your help. This works well, and there is also an automated VBA solution in a related question I asked yesterday at http://www.mrexcel.com/forum/showthread.php?t=616660

 

[circledchicken]

Your welcome - that's a very nice solution from Peter_SSs on your other post!