CODEWITHSUNDEEP
excelpivotexcel-2010pivot-table
deostroll
deostroll

Reputation: 11975

Merging two columns for excel pivot report

I have data in my excel sheet that looks like the follows:

enter image description here

For the sake of explanation I have hidden some columns (Please ignore them). I have taken pivot of the view you see above and the output of that is as follows:

enter image description here

However, I am expecting an output similar to the following, where the L1 and L1C (which denote the same thing) are displayed as one entity (as if it came from one column itself)

enter image description here

Does excel's pivot feature provide something to this effect?

ps: if you want access to data I've used visit my google doc file: click here

Update:

What I had done to achieve the desired result was generate the desired columns via excel formulas. (Hence you would notice columns after the Name field in the spreadsheet I shared above). Is this the only way you can approach this problem in excel during pivoting?

Upvotes: 2

Views: 8145

Answers (1)

lori_m
lori_m

Reputation: 5567

Having set up the data and pivot as in the first two screenshots, you could select a cell in the pivot table and try running this code to get the last screenshot:

Sub UpdatePivot()
With ActiveCell.PivotTable
    AddPivotLabels .PivotFields("l1"), .PivotFields("l1c")
    AddPivotLabels .PivotFields("l2"), .PivotFields("l2c")
    AddPivotLabels .PivotFields("l3"), .PivotFields("l3c")
End With
End Sub

Sub AddPivotLabels(pName1 As PivotField, pName2 As PivotField)
Dim i As Long
For i = 1 To pName1.PivotItems.Count
    pName1.PivotItems(i).Value = pName1.PivotItems(i).SourceName & _
                            " (" & pName2.PivotItems(i).SourceName & ")"
Next i
End Sub

Note: This assumes that the relationships are 1-1 you probably want to check this before running on larger datasets.

Upvotes: 1

Related Questions