1

I have data in the following format

Project Person1 Person2 Person3
A         Joe     Alice  Bob
B         Bob      Tom   Joe
C         Alex     Alice  Tom
D         Bob      Doug   Susan

How can I get a pivot table that shows the following:

Person   Prj1    Prj2 Prj3
Joe       A       B 
Alice     A       C
Bob       A       B    D
Tom       B       C   
Dog       D
Susan     D
Alex      C
user1357015
  • 133
  • 1
  • 2
  • 11

2 Answers2

1

Hit Alt-D then P to open the legacy pivot table wizard.

  • In Step 1 click "Multiple consolidation ranges" and Next
  • In Step 2 click "I will create the page fields" and Next
  • In Step 3 select the table range and click Add, then Finish
  • The pivot table will be created in a new worksheet.
  • In the pivot field list, drag any items out of the row and the columns area, so only the Value box is populated. This will result in a single pivot table cell labelled "Count of Value.
  • Double-click the number in that cell.
  • a new sheet will appear that contains the flattened data source for the table.
  • delete the Person column and rename the first column "Project"
  • you can now delete the sheet with the pivot table.

Use the newly created table to build your desired report.

teylyn
  • 23,615
0

I would resolve this with the Power Query Add-In. It takes a few steps to get there and a bit of coding in the Power Query language (M) to generate the "running count" needed to get the "Prj1/2/3" column headings.

This is similar to the following question which I answered previously, and built a working demo which you can download and use:

Excel: How to extract group of the same rows and their values into columns?

That question had one extra requirement - to filter the result set. Just delete that last step in the Query.

Community
  • 1
Mike Honey
  • 2,632