I have a list of development tasks which I track in Excel.

These tasks support 1 or more business objectives.

I want to create a pivot table that shows a matrix of what tasks support what objectives.


I can only have one row per task, as it has lots of other tracking columns.
But I want to add, say, 4 columns where we can select the objectives...

e.g:

Raw data

Task Obj1 Obj2 Obj3 Obj4
T1 OB008 OB009 OB011
T2 OB004 OB008 OB003 OB012
T3 OB004 OB011 OB003
T4 OB002 OB008 OB011 OB001

What I want (showing an x or just a 1 is fine)
(see attached mockup)

Task OB001 OB002 OB003 OB004 OB008 OB009 OB011 OB012
T1 x x x
T2 x x x x
T3 x x x
T4 x x x x


Effectively it's treating all the Obj* columns in the raw data as one column, or perhaps it's expanding it out to normalise this denormalised data... not sure, but either way I'm sure there's a way to do it but can't figure it out.

I did see this post but didn't quite make sense or may be trying to do a different thing... http://www.excelbanter.com/showthread.php?t=3748

Thanks very much

Peter
Wellington
New Zealand