Pivot Tables: Possible to apply one filter to multiple columns?

[averagegirl]

Hello! Here is my dilemma. My master chart, from which I want to create a pivot table, looks something like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Client[/TD]
[TD]Project[/TD]
[TD]Senior Person[/TD]
[TD]Senior Person[/TD]
[TD]Senior Person[/TD]
[TD]Junior Person[/TD]
[TD]Junior Person[/TD]
[TD]Junior Person[/TD]
[/TR]
[TR]
[TD]Client 1[/TD]
[TD]Project A[/TD]
[TD]John Lennon[/TD]
[TD][/TD]
[TD][/TD]
[TD]Britney Spears[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client 2[/TD]
[TD]Project B[/TD]
[TD]John Lennon[/TD]
[TD]Ringo Starr[/TD]
[TD][/TD]
[TD]Selena Gomez[/TD]
[TD]Taylor Swift[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client 2[/TD]
[TD]Project C[/TD]
[TD]Paul McCartney[/TD]
[TD][/TD]
[TD][/TD]
[TD]Britney Spears[/TD]
[TD]Usher[/TD]
[TD]Selena Gomez[/TD]
[/TR]
[TR]
[TD]Client 3[/TD]
[TD]Project D[/TD]
[TD]John Lennon[/TD]
[TD]Paul McCartney[/TD]
[TD]George Harrison[/TD]
[TD]Justin Bieber[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I want to create a pivot table that allows me to generate a list of all the projects that a particular Senior Person is working on. And I would ideally like to do this with only one filter. E.g., I want to be able to select "Paul McCartney" from one filter and get a list that includes both Client 2/Project C and Client 3/Project D (even though Paul McCartney's name appears into two different Senior Person columns).

If this isn't possible because I have three Senior Person columns, is there another solution that would get me the result? Originally, the table looked like this, but I was told that the Pivot Table would always recognize "John Lennon" and "John Lennon; Ringo Starr" as separate entities rather than two instances of "John Lennon."

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Client[/TD]
[TD]Project[/TD]
[TD]Senior Person[/TD]
[TD]Junior Person[/TD]
[/TR]
[TR]
[TD]Client 1[/TD]
[TD]Project A[/TD]
[TD]John Lennon[/TD]
[TD]Britney Spears[/TD]
[/TR]
[TR]
[TD]Client 2[/TD]
[TD]Project B[/TD]
[TD]John Lennon; Ringo Starr[/TD]
[TD]Selena Gomez; Taylor Swift[/TD]
[/TR]
[TR]
[TD]Client 2[/TD]
[TD]Project C[/TD]
[TD]Paul McCartney[/TD]
[TD]Britney Spears; Usher; Selena Gomez[/TD]
[/TR]
[TR]
[TD]Client 3[/TD]
[TD]Project D[/TD]
[TD]John Lennon; Paul McCartney; George Harrison[/TD]
[TD]Justin Bieber[/TD]
[/TR]
</tbody>[/TABLE]

So that's why I tried splitting them up into their own columns. (Btw, I did try giving each person his/her own row, which works for the pivot table, but that results in the master chart showing multiple rows for the same project. This is something that I want to avoid.) Anyway, any help on this would be much appreciated! Thanks so much!

 

[texasalynn]

You can not create a pivot table with columns that have the same header titles. In your original layout you could create autofilters and filter with "contains" John Lennon.

 

[averagegirl]

You can not create a pivot table with columns that have the same header titles. In your original layout you could create autofilters and filter with "contains" John Lennon.

Thank you, I think this works for me!