Hi I currently have a data set which looks like this:
c1.jpg

I want to be able to be able to create either of the following dynamic view using a pivot or a formula only. I would prefer not to use a macro but if that is the only way then I guess I will have to.

View 1: c2.jpg

or

View 2: c3.jpg

Basically I have the data which spans 1000 rows and 400 columns and I want to be able to dynamically transform it as either View 1 or View 2 so that I could run a filter on Title (i.e. a, b, c or d) and get all the PBS-xxxx that are relevant for each Title.

Please any help would be appreciated. I have attached the spreadsheet below.


Thanks

PivotDataSet.xls