Showing resources per month in a pivot table

I am trying to view number of resources assigned to projects by month. I am collecting information in the format below from engineering managers, and want to convert this into a PivotTable with the sum of resources per project by month. This works for a single month - if I create a pivot table for October only, I get the correct count of resources by project for October. However, if I add in November to my pivot table, Project 3 or Project 4 won't show up in the rows since they aren't listed in October. I'm not sure if Pivot Tables are the best option for how I want to show the information, so was hoping somebody could offer some advice? Can pivot tables do what I'm looking for, or should I be using some other Excel tools? I've also attached a sample sheet with this data if that helps. Thanks for any help you can give.


Format for collecting information
|Resource Name| October | November | December|
|Bob| Project 1 | Project 1 | Project 2|
|Alice| Project 1 | Project 3| Project 1|
|Mike| Project 2 | Project 4 | Project 2|

Format to present information

|Project | October | November | December|
|Project 1| 2 | 1 | 1 |
|Project 2| 1 | 0 | 2 |
|Project 3| 0 | 1 | 0 |
|Project 4| 0 | 1 | 0 |

A pivot table can be used, but the data needs to be normalized as shown in columns F:H. You can keep or remove the grand totals. Also be careful with spelling. Some of your project names had trailing spaces which made them appear to be duplicates on the pivot table.

There is a way to do it in PowerPivot. I am not familiar with it. I can see why people like PowerPivot but most of my work isn't set up to take advantage of it. So I don't know it well. Someone else might want to chime in with that solution.

In this particular case, I normalized the data manually. I suggest you set up your system to do data entry in this format.

However, if you have a lot of legacy data to convert, there is a tool for that: https://www.excelforum.com/tips-and-...zing-data.html

Rather than trying to adapt the tool to your spreadsheet. It might be easier to download the tool and put your data in the raw data sheet. Then configure things in accordance with the instructions in the word document on the Control Panel sheet.