• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot Table - Excel 07 - FIlter using Contains

R

rjacmuto32

Member
I'm using excel 2007, and I have a pivot table with a field for country. Most of my records have the country populated like United States,Canada or Canada,Spain,China in the field. Is there a way to filter the pivot table field using Contains? I want to see only items containing say Canada in my Pivot Table?


Pivot table has the option grayed out for Contains, but you can filter on contains in a regular Advanced Filter. My pivot table is in a cool dashboard with charts and stuff all linked up to it, so anyway to just filter a pivot table field on contains would be great.


I know you can do that in excel 2010, but our company is behind the times in upgrading Office.


Thanks,

--Robert
 
Dropbox Blocked at work.


You can just do a simple 3 column data table with a couple rows of data. In one column for country, put United States,Canada in say cell C2 and C4 and Canada,Spain,China C3. Simple pivot table the 3 fields and try to filer for contains "China"
 
ID Name Country Volume

1 Blocks Canada,Spain,China 5

2 Blanks United States,Canada 1

3 Cubes Mexico 12

4 Blanks China 10

5 Blocks Mexico,China 7


When you do a simple Pivot table and you only put Country and then use Volume for the total. I need a way to filter the pivot table on Country using Contains? Filter the pivot table (not the data itself) for items containing China. So the pivot table would only show me #'s 1,4,5.
 
Hi, rjacmuto32!


Give a look at this file, and check PT at F1 cell, it's filtered as you required, I guess:

https://dl.dropbox.com/u/60558749/Pivot%20Table%20-%20Excel%2007%20-%20FIlter%20using%20Contains%20%28for%20rjacmuto32%20at%20chandoo.org%29.xlsx


Just advise if any issue.


Regards!
 
Good day Robert

I have taken your data in to a table


https://dl.dropbox.com/u/75495784/Pivot%20chart.xlsx
 
Hi, rjacmuto32!


Well, let me see if this works:

a) open Excel, new workbook, your data in sheet Sheet1 from cells A1:D6

b) select table, Insert tab, Table group, Dynamic Table icon, Existing Worksheet option, Location F1 cell

c) select fields clicking on Country and Volume

d) click on cell F1 (Row labels), select Filter By Labels, Contains, input China


Et voilá, c'est tout!


Hope it works until you can download the file.


Regards!
 
That works but it's not what i'm looking for. That is done from a Table. I need to do it in a Pivot Table. Create a simple pivot table with the data. Need a way to filter a pivot field by contains. I have other items and stuff all linked off the pivot table that is constantly updated whenever a user makes a seleection on a dyncamic dashboard. How can it be done on a Pivot Table Field?
 
I have a code to filter a pivot table by an exact item below. How would you change this to work to filter the field with Contains? Instead of Value = need value contains.


'sCriteria = Range("AM3").Value

'ActiveSheet.PivotTables("PivotTable2").PivotFields("Country").PivotFilters _

' .Add Type:=xlCaptionEquals, Value1:=sCriteria
 
Hi, rjacmuto32!


I apologize for my English traduction of Excel menus. Where I wrote "Dynamic Table" (in my Spanish version "Tabla dinámica", first icon of Table group; second is "Table" -"Tabla"-) I should have written "Pivot Table").


Regards!
 
Hi, rjamcmuto32!


Using VBA you can do this:

- Add a filter: ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("Country").PivotFilters.Add Type:=xlCaptionContains, Value1:="China"

- Clear all filters: ActiveSheet.PivotTables("Tabladinámica1").PivotFields("Country").ClearAllFilters


(Operating on same workbook as uploaded)


Regards!
 
That code line for contains debugged in excel 2007. it didn't work


'ActiveSheet.PivotTables("PivotTable2").PivotFields("Country").PivotFilters.Add Type:=xlCaptionContains, Value1:="China"


I will try that excel file tonight that was posted and take a look
 
Hi, rjacmuto32!

I'm using the 2010 version, but I don't remember if that wasn't available in 2007, sorry.

But despite of that VBA way, did you try to manually doing it as describe upwards?

Regards!
 
It's not in excel 2007. The fields for contains are grayed out for a pivot table. I read you can do that in excel 2010 which you are using. I was hoping some VBA code will do the trick. Guess not if that other file doesn't work.


I will have to get our management to upgrad to 2010. I read some articles about Slices in Pivot Tables which sounds very useful.
 
Bob,


What excel did you create that file in? 2007 or 2010?


I can see on your dropbox file the setting of Series LAbel and can do a contains. However when I tried to repliacte that in excel 2007 which is what I have, it still wasn't available.
 
Good Evening rjacmuto32


I did it it Excel 2010........but to my knowledge the only thing you would not have is the slicers, every thing else, the table,pivot table and chart should be available in 2007.

I have seen videos for tables and pivot tables and apart from the location difference on the ribbon I got the impression they where the same.

If you have down;loaded the fill and can work with, ie doing a contains and that works not sure as to why the rest does not
 
Hi, rjacmuto32!

Could you download and try my nearly rude (because of simple and regarding b(ut)ob(ut)hc's sophisticated example) uploaded file?

Regards!
 
When a Nija of such fame as SirJB7 use the word "sophisticated" regarding a reply from a lowly board member it is time for a large glass of malt..........but to me pivot tables and tables are simple to use once you have been told how to use them, as I was in my first posts :)
 
@b(ut)ob(ut)hc

Hi, old dog!

Good night for you, I assume.

Don't make me blush, remember my shyness. And as I suppose you have done yet, when you compare my simple and humble and trivial and two-penny workbook with your high-end and state-of-the-art workbook, the words rude and sophistication come immediately for seldom suggestions.

I see that this semester efforts are showing it results. Congrats and have a Carlsberg!

Regards!
 
You must log in or register to reply here.
Back
Top