Filter from multiple data fields in a pivot table

Question

Hello together,

I currently have the following challenge in Excel: I created an overview of different projects in our company. Each project is managed by up to 4 team members. They may appear in different order for each individual project, e.g. Project 1 is managed by Person 1, Person 3 and Person 4, Project 2 is managed by Person 2 and Person 1, etc.

The columns are as follows: "Project", "Involved 1", "Involved 2", "Involved 3" and "Involved 4"

I now want to create an overview in a pivot table. However, I want to be able to choose a specific team member and see the projects they are involved in.

Is it possible to combine the data entries for "Involved 1", "Involved 2", "Involved 3" and "Involved 4" into one single filter? I only want to have the filter "Involved" that allows to select the individual persons and shows the projects they are involved in.

Answer 1

Hi,

Your data is not well structured.  In the Query Editor, right click on the first column and select "Unpivot other columns".  This will convert your dataset into a 3 column one.  Click on Close and Load.  Now build a Pivot Table from this rehashed dataset.

Hope this helps.

Answer 2

Excel 365 Pro Plus with PowerPivot and Power Query.

Pivot with Teams, Persons and Project.

All text.

http://www.mediafire.com/file/rc4jqd41yq56xmt/04_09_20.xlsx/file

http://www.mediafire.com/file/pp2gqt5il2p439u/04_09_20.pdf/file

Answer 3

Hi ymehl,

I go though the post. instead of using pivot table, I think you may use VLOOKUP formula to get desired result in the table, as you can see data in screenshot below:

 

Formula in I10:

=VLOOKUP("Project "&J2,A2:E5,J3+1)

Hope these can help you.

Regards,

Clark