Creating Dynamic Analysis

[mikeymay]

I have a set of data that includes the following fields

• Role ID
• Client
• Project Code
• Role

The data in the images is sample data and the Client & Project Codes will be free text when entered and will not have any consistent prefix or suffix.

The data is set out as following in the sheet

I need to create the following reporting style

I thought utilising a Pivot Table to get the data in the row order would work, but it places all the data in the same column

I am really struggling to get this to work and wondered if anyone has had a similar problem and resolved it.

 

[James006]

Hello,

From your output in the Pivot Table ... would it be feasible for you to test Len() of each celll in order to segregate them into three columns ...???

 

[mikeymay]

I have spoken with the client about this and they have expressed an interest in utilising VBA code.....much easier!!!

 

[RoryA]

Just change the pivot table design to use tabular layout?

 

[mikeymay]

ADVERTISEMENT

I tried this (do you mean Classic?) but the 1st instances of each element appeared in the same row.

Is there a different layout option available?

 

[James006]

Thanks RoryA ...

http://www.contextures.com/excelpivottablereportlayout.html

 

[RoryA]

Sorry, I meant Outline, not tabular.

 

[mikeymay]

Just what I needed!

Thanks RoryA