Pivot merging multiple text columns into one..

[cdrako]

Hey y'all, I've been racking my brain on this for a few days. Maybe it's the holidays but I just can't seem to get it working right.

I'm putting together a tool to report our portfolio of projects across the organization. Then leveraging Pivots, I have a tab with charts and slicers that the execs can then view the data. (using Excel 2010)

My issue is that some projects impact multiple hospitals... That fields, using VBA is a multi select for ease of entry for execs, so I then have multiple columns (one for each hospital) so that I can do counts in a pivot.

Data Example:

[TABLE="width: 827"]
<tbody>[TR]
[TD]Project[/TD]
[TD]Region[/TD]
[TD]Hospital(s)[/TD]
[TD]Resources[/TD]
[TD]St. Joe's[/TD]
[TD]St. Mary's[/TD]
[TD]St. Ann's[/TD]
[TD]St. Luke's[/TD]
[TD]St. Catherine's[/TD]
[TD]St. Mark's[/TD]
[TD]St. John's[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]NORTH[/TD]
[TD]St. Joe's[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD]NORTH[/TD]
[TD]St. Joe's; St. Mary's; St. Ann's[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project 3[/TD]
[TD]WEST[/TD]
[TD]St. Luke's; St. Catherine's[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project 4[/TD]
[TD]WEST[/TD]
[TD]St. Catherine's[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project 5[/TD]
[TD]WEST[/TD]
[TD]St. Luke's[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project 6[/TD]
[TD]NORTH[/TD]
[TD]St. Ann's; St. Mary's[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project 7[/TD]
[TD]SOUTH[/TD]
[TD]St. Mark's[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project 8[/TD]
[TD]SOUTH[/TD]
[TD]St. Mark's, St. John's[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]


What I think I need is a pivot, for a slicer to be able to filter the projects by hospital, that combines them something like:

[TABLE="width: 307"]
<tbody>[TR]
[TD]Project[/TD]
[TD]Region[/TD]
[TD]Hospital(s)[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]NORTH[/TD]
[TD]St. Joe's[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD]NORTH[/TD]
[TD]St. Joe's[/TD]
[/TR]
[TR]
[TD]Project 3[/TD]
[TD]NORTH[/TD]
[TD]St. Mary's[/TD]
[/TR]
[TR]
[TD]Project 4[/TD]
[TD]NORTH[/TD]
[TD]St. Ann's[/TD]
[/TR]
[TR]
[TD]Project 3[/TD]
[TD]WEST[/TD]
[TD]St. Luke's[/TD]
[/TR]
[TR]
[TD]Project 4[/TD]
[TD]WEST[/TD]
[TD]St. Catherine's[/TD]
[/TR]
[TR]
[TD]Project 4[/TD]
[TD]WEST[/TD]
[TD]St. Catherine's[/TD]
[/TR]
[TR]
[TD]Project 5[/TD]
[TD]WEST[/TD]
[TD]St. Luke's[/TD]
[/TR]
[TR]
[TD]Project 6[/TD]
[TD]NORTH[/TD]
[TD]St. Ann's[/TD]
[/TR]
[TR]
[TD]Project 7[/TD]
[TD]NORTH[/TD]
[TD]St. Mary's[/TD]
[/TR]
[TR]
[TD]Project 7[/TD]
[TD]SOUTH[/TD]
[TD]St. Mary's[/TD]
[/TR]
[TR]
[TD]Project 8[/TD]
[TD]SOUTH[/TD]
[TD]St. Mark's, St. John's[/TD]
[/TR]
[TR]
[TD]Project 9[/TD]
[TD]SOUTH[/TD]
[TD]St. John's[/TD]
[/TR]
</tbody>[/TABLE]

I would create a data source that has 1 line per hospital instead of per project but our resources, budgets etc are assigned by project, not hospital.

Does this make sense?

Thank you for any help you could provide..this is making me nuts...

Cindy