So, I have excel 2007 and data similar to the below. Each ID represents a case file and the color a type of error (green is no errors), so there may be more than one on the same case:
[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]ID[/TD]
[TD="width: 64"]Name[/TD]
[TD="width: 64"]Colour[/TD]
[/TR]
[TR]
[TD="class: xl65"]12345[/TD]
[TD]Dave[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD="class: xl65"]12345[/TD]
[TD]Dave[/TD]
[TD]Yellow[/TD]
[/TR]
[TR]
[TD="class: xl65"]12345[/TD]
[TD]Dave[/TD]
[TD]Yellow[/TD]
[/TR]
[TR]
[TD="class: xl65"]1578[/TD]
[TD]Bob[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD="class: xl65"]1578[/TD]
[TD]Bob[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD="class: xl65"]123[/TD]
[TD]Dan[/TD]
[TD]Yellow[/TD]
[/TR]
[TR]
[TD="class: xl65"]99999[/TD]
[TD]Dave[/TD]
[TD]Yellow[/TD]
[/TR]
[TR]
[TD="class: xl65"]99999[/TD]
[TD]Dave[/TD]
[TD]Yellow[/TD]
[/TR]
[TR]
[TD="class: xl65"]1587[/TD]
[TD]Borris[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD="class: xl65"]5555[/TD]
[TD]Borris[/TD]
[TD]Red
[/TD]
[/TR]
</tbody>[/TABLE]
There is a hierarchy of Red then Yellow so where both exist on a case, it counts as a red case. I want to produce a pivot table with the each unique name and their total number of unique cases, broken down into percentage (or count) of Red, Yellows and Greens.
I've been trying to add helper columns to the underlying data but I cant get accurate results. Help!
[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]ID[/TD]
[TD="width: 64"]Name[/TD]
[TD="width: 64"]Colour[/TD]
[/TR]
[TR]
[TD="class: xl65"]12345[/TD]
[TD]Dave[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD="class: xl65"]12345[/TD]
[TD]Dave[/TD]
[TD]Yellow[/TD]
[/TR]
[TR]
[TD="class: xl65"]12345[/TD]
[TD]Dave[/TD]
[TD]Yellow[/TD]
[/TR]
[TR]
[TD="class: xl65"]1578[/TD]
[TD]Bob[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD="class: xl65"]1578[/TD]
[TD]Bob[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD="class: xl65"]123[/TD]
[TD]Dan[/TD]
[TD]Yellow[/TD]
[/TR]
[TR]
[TD="class: xl65"]99999[/TD]
[TD]Dave[/TD]
[TD]Yellow[/TD]
[/TR]
[TR]
[TD="class: xl65"]99999[/TD]
[TD]Dave[/TD]
[TD]Yellow[/TD]
[/TR]
[TR]
[TD="class: xl65"]1587[/TD]
[TD]Borris[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD="class: xl65"]5555[/TD]
[TD]Borris[/TD]
[TD]Red
[/TD]
[/TR]
</tbody>[/TABLE]
There is a hierarchy of Red then Yellow so where both exist on a case, it counts as a red case. I want to produce a pivot table with the each unique name and their total number of unique cases, broken down into percentage (or count) of Red, Yellows and Greens.
I've been trying to add helper columns to the underlying data but I cant get accurate results. Help!