Can you display column header as text in pivot?

[sryan429]

Hello,
I'm hoping this is an easy one, but am having a difficult time articulating. So sorry and thank you in advance I have a list that I'm displaying in tabular form pivot, but I want each of the 2nd data points to be listed in it's own column and use the column header as the field value, NOT a count/sum/etc.

For a generic example, here is the raw data

Excel Workbook
	A	B	C	D	E	F	G	H
4	Raw Data	*	*	*	*	*	*	*
5	Animal	Color	*	*	*	*	*	*
6	Dog	Red	*	*	*	*	*	*
7	Cat	Blue	*	*	*	*	*	*
8	Mouse	Yellow	*	*	*	*	*	*
9	Bird	Yellow	*	*	*	*	*	*
10	Squirrel	Green	*	*	*	*	*	*
11	Dog	Yellow	*	*	*	*	*	*
12	Cat	Red	*	*	*	*	*	*
13	Bird	Red	*	*	*	*	*	*
14	Bird	Orange	*	*	*	*	*	*
15	Bird	Blue	*	*	*	*	*	*
16	*	*	*	*	*	*	*	*
17	Do NOT want tabular. *Looking for colors in column	*	*	*	*	*	*	*
18	Animal	Color	*	*	*	*	*	*
19	Bird	Blue	*	*	*	*	*	*
20	*	Orange	*	*	*	*	*	*
21	*	Red	*	*	*	*	*	*
22	*	Yellow	*	*	*	*	*	*
23	Cat	Blue	*	*	*	*	*	*
24	*	Red	*	*	*	*	*	*
25	Dog	Red	*	*	*	*	*	*
26	*	Yellow	*	*	*	*	*	*
27	Mouse	Yellow	*	*	*	*	*	*
28	Squirrel	Green	*	*	*	*	*	*
29	Grand Total	*	*	*	*	*	*	*
30	*	*	*	*	*	*	*	*
31	When I put Color in columns I see count/sum/etc as options for data, I want to see text instead	*	*	*	*	*	*	*
32	*	*	*	*	*	*	*	*
33	Row Labels	Blue	Green	Orange	Red	Yellow	Grand Total	*
34	Bird	1	*	1	1	1	4	*
35	Cat	1	*	*	1	*	2	*
36	Dog	*	*	*	1	1	2	*
37	Mouse	*	*	*	*	1	1	*
38	Squirrel	*	1	*	*	*	1	*
39	Grand Total	2	1	1	3	3	10	*
40	*	*	*	*	*	*	*	*
41	What I'd like to see. *Value in column field matching the column header	*	*	*	*	*	*	*
42	*	*	*	*	*	*	*	*
43	Animal	Blue	Green	Orange	Red	Yellow	*	*
44	Bird	Blue	*	Orange	Red	Yellow	*	*
45	Cat	Blue	*	*	Red	*	*	*
46	Dog	*	*	*	Red	Yellow	*	*
47	Mouse	*	*	*	*	Yellow	*	*
48	Squirrel	*	Green	*	*	*	*	*
Sheet1

Any thoughts?

 

[pbornemeier]

As far as I know the only things that can be in the data part of the Pivot Table are numbers.
This code will copy the PT to another worksheet (named ModPT) and convert the numbers (as shown in rows 34-39 above) to the Header Text

Option Explicit

Sub ShowPTDataWithHeaderValues()

    Dim sWorksheet As String
    Dim sPTSheetName As String
    Dim lLastRow As Long
    Dim lLastCol As Long
    Dim lRowIndex As Long
    Dim lColIndex As Long
    Dim sHeader As String
    
    If ActiveSheet.PivotTables.Count <> 1 Then
        MsgBox "Start this code on a worksheet with one PivotTable ", , "Exiting"
        GoTo End_Sub
    End If
    sWorksheet = "ModPT"
    sPTSheetName = ActiveSheet.Name
    
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets(sWorksheet).Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    Worksheets.Add(After:=Sheets(Sheets.Count)).Name = sWorksheet 'After last
    
    Worksheets(sPTSheetName).Range("A3").CurrentRegion.Copy
    
    With Worksheets("ModPT")
        .Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        .Rows("1:1").Delete Shift:=xlUp
        lLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        lLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        If InStr(.Cells(lLastRow, 1).Value, "Grand Total") > 0 Then lLastRow = lLastRow - 1
        If InStr(.Cells(1, lLastCol).Value, "Grand Total") > 0 Then lLastCol = lLastCol - 1
        
        For lColIndex = 2 To lLastCol
            sHeader = .Cells(1, lColIndex).Value
            For lRowIndex = 2 To lLastRow
                If .Cells(lRowIndex, lColIndex).Value > 0 Then
                    .Cells(lRowIndex, lColIndex).Value = sHeader
                End If
            Next
        Next
        
    End With
    
End_Sub:

End Sub