Pivottable

[hgus393]

Hi, I have a little problem with a pivottable that I wonder if it could be resolved in VBA. I have a Pivottable that shows Name, surname, title, subtitle and sales amounts (as calculated field). Ok now for the problem. If there is the same name, surname and the same title but different sub title it only shows one row for the title. Is it possible to find the title in the data sheet and increment the title with a space for each time it occurs. Ie if there is two Peter Smith with the title Boss and with a different subtitle (ie sales manager and Relationship manager) is it possible to increment the second Boss with one space and if there where more increment them accordingly? Ex

Peter Smith Boss sales manager
Peter Smith Boss Relationship manager (increment Boss with an extra space at end)
Peter Smith Boss Genereal manager (increment Boss with 2 extra spaces at end) and so on...

Cheers

Rob

 

[pauljamestg]

Is it really necessary to have two different columns for Title and Subtitle? If it is ok to concatenate that info in one column, then your problem might be sorted easily. May be you would have thought of this option. But just to be sure...

 

[hgus393]

Is it really necessary to have two different columns for Title and Subtitle? If it is ok to concatenate that info in one column, then your problem might be sorted easily. May be you would have thought of this option. But just to be sure...

Yeah tried that, but management didn't like that...

 

[moonfish]

Pivot tables are for grouping data. If you don't want to group people by their name, surname and title you should stop using pivot tables.

If you are allowed to alter your source data, just add the desired space in the subtitle in your source data. Or add a column to your source data that does this for you.

 

[RoryA]

If you are using 2010 and you are simply trying to fill in all the blanks, there is an option for that. In 2007 or prior, you would need to copy and paste the table elsewhere and then fill in the blanks.