Overwriting text in the 'Rows' area of a Pivotable

[pjmorris]

I have a fairly simple pivotable, produced by adding the data to the Data Model and then Powerpivot to display (this is critical as the problem is not apparent in a simple Pivotable), laid out in tablular format. Someone (me!) has typed text over the information provided in one cell in one of the columns created by the 'Rows' of the field list and I can't revert to the correct text. However the pivotable shows the correct data in the values area so it knows to what the row refers. It's as if it has applied a pseudonym to the data coming from the source.

Does anyone know how to get it to show the correct information. I've tried removing the particular row, refreshing, and then renewing it, but with no success.

For example (apologies for layout, but I can't do otherwise on a work computer), three columns and one value:

column1	column2	column3	value
a	200​	Cups	1​
a	210​	Saucers	2​
a	220​	Plates	3​
a	230​	Medals	4​
b	200​	Cups	1​
b	210​	Saucers	2​
b	220​	Plates	3​
b	230​	Medals	4​
e	310​	knives	1​
e	320​	Forks	2​
e	330​	Spoons	3​
e	340​	Napkins	4​

produces this Pivotable (using PowerPivot) where you can see that the text in Column3 against the 220 rows should be 'Plates' but is 'aaaaa', I need to revert to 'Plates':

column1	column2	column3	Sum of value
a	200​	Cups	1​
	210​	Saucers	2​
	220​	aaaaa	3​
	230​	Medals	4​
b	200​	Cups	1​
	210​	Saucers	2​
	220​	aaaaa	3​
	230​	Medals	4​
e	310​	knives	1​
	320​	Forks	2​
	330​	Spoons	3​
	340​	Napkins	4​

Grateful for any suggestions.

Regards

 

[Alex Blakenburg]

Below is a modified version of Debra Dalgleish's code to reset the captions for a single item that can be found here:
Excel Pivot Item Macros
look for the heading "For Normal or OLAP Pivot Tables"

You need to select one of the items in the Row Labels column with the descriptions you want to change and then run the code.
It will use that item to identify the Pivot Field and run through all the Items in that field resetting any that have had their captions changed.

Sub FixPivotItemCaptionDual_Mod()
Dim pt As PivotTable
Dim pi As PivotItem
Dim lRsp As Long
Dim strCap As String
Dim strSN As String
Dim pf As PivotField
  
Dim lNum As Long
Dim lFind As Long
Dim strFind As String
Dim strCap01 As String
Dim strCap02 As String

On Error Resume Next
strFind = "&"
  
On Error Resume Next
Set pi = ActiveCell.PivotItem
Set pt = pi.Parent

If pi Is Nothing Then
  MsgBox "Please select a pivot item label cell"
Else
  Set pf = pi.Parent
  strCap = pi.Caption
  strSN = pi.SourceName
  If pt.PivotCache.OLAP Then
    For Each pi In pf.PivotItems
        strCap01 = pi.SourceName
        lFind = InStrRev(strCap01, strFind) _
                + Len(strFind) - 1
        strCap02 = Replace(Replace(Replace(strCap01, _
          Left(strCap01, lFind), ""), _
            "[", ""), "]", "")
        If pi.Caption <> strCap02 Then pi.Caption = strCap02
    Next pi
  Else
    For Each pi In pf.PivotItems
        strCap02 = pi.SourceName
        If pi.Caption <> strCap02 Then pi.Caption = strCap02
    Next pi
  End If
End If

End Sub

 

[RoryA]

Can't you just type Plates in one of the aaaaa cells?

 

[pjmorris]

Alex Blakenburg - many thanks - I'll try that when I'm next on the work computer. Appreciated.

RoryA - that is what I've done as an interim solution, but it doesn't feel as if its corrected the problem, merely created another synonym (wrong word in original post). If that has really solved the problem - then many thanks. My concern is because I don't know what has really happened.

Thanks again to you both for replying.

 

[pjmorris]

I've just recreated the table and pivot. Alex's code (not surprisingly) did exactly what was required! It also allowed me to see what had happened and Debra's explanation assisted. I can see that Rory's suggestion does exactly what the code does, though it assumes you know the caption should be.

Thanks again for your help. - really appreciated. I've marked Alex's reply as 'Answer' as it is potentially more comprehensive, though I would happily have marked both as such.

 

[Alex Blakenburg]

You're welcome. Glad we could help.