PIVOT table 'puzzled'

[pecochran1]

Ugh, I can't seem to get my head wrapped around this problem. I am trying to get a list of people on projects based on if they have scheduled time on the project. My data is set up in the example below(actual table is >900 people and >75 projects):

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]ProjectA
[/TD]
[TD]ProjectB
[/TD]
[TD]ProjectC
[/TD]
[TD]ProjectD
[/TD]
[/TR]
[TR]
[TD]Name1
[/TD]
[TD]20
[/TD]
[TD][/TD]
[TD]20
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name2
[/TD]
[TD][/TD]
[TD]15
[/TD]
[TD][/TD]
[TD]25
[/TD]
[/TR]
[TR]
[TD]Name3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name4
[/TD]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD]30
[/TD]
[/TR]
</tbody>[/TABLE]

Can a pivot table produce a simple format such as below? I don't even need the numeric values:

ProjectA

Name1
Name3
​

ProjectB

Name2​

ProjectC

Name1​

ProjectD

Name2
Name4

Thank you
​

 

[ghrain22]

Make both items row fields. with the top field being whatever is labeling the project

 

[bkaehny]

I don't think so. Your data isn't exactly in the right format for a pivot table to give you what you want. This is how your data would need to be structured:

Name|Project|Value
Name1|ProjectA|20
Name1|ProjectC|20
Name2|ProjectB|15
etc.

If your data were like that, you'd just put the Project and Name fields into the Row Headings, and you'd have exactly what you want.

 

[Weazel]

here is a video on how to 'unwind' the data to make it pivot table friendly.

Learn Excel - Unwind Pivot Data - Podcast 1787 - YouTube

 

[pecochran1]

Thank you all. I will experiment with each of your responses.

Cheers