Converting multiple variables into values with excel pivot tables or power pivot

Question

I have to use excel for this problem. I need to know how to create a pivot table from a very large dataset that's simplified structured looks like this:

      week 1    week 2  week 3

row 1
row 2
row 3
row 4

I need to use a slicer to use only specific weeks but change the week simply. If I weren't constrained by excels number of rows, I would create a "DATE" variable, and have "week 1, week 2, week 3..." be values for that variable, but this results in too many rows (using 52 weeks, so number_of_rows*52 rows quickly maxes out to 1 million).

Is it possible to create a pivot table in excel with a slicer on "week X" such that the values for each row are displayed for the desired week without creating a new variable and using increasing the number of rows significantly?

I can do this in another language, I'm constrained by the work skills of others, and thus need a way to do this in a simple tool for them to use.

you could try using an OFFSET defined named which would be based of a single cell with a drop down for the week selection, which would return required column that you could feed into a pivot table? However it also depends on the actual data set as this would be only a viable solution for a single column of data to return — Raugmor
1. I am unfamiliar with OFFSET, can you explain further or provide reference? 2. To clarify your point, you're saying that if I had column TOTAL after all the weeks, I would not be able to present both "week x" and "TOTAL"? — Lisle

jeffreyweir jeffreyweir · Accepted Answer · 2015-09-27T20:54:02

A bit of VBA will get you there. Create a new PivotTable from a data source that contains nothing but the weeks that you want to show up in the slicer. i.e. 'Week 1', 'Week 2' etc. Create a slicer for that PivotTable, and when a user clicks on it, catch the resulting PivotTable_Update event and use it to change which weeks fields shows up in your existing master PivotTable.

---Edit--- Here's how that looks:

...and here's what happens if I select a singe field from that Weeks slicer:

...so as you can see, when you click the 'dummy' slicer then the fields get switched out in the PivotTable to match the slicer selection.

Here's the code that lets you do that. This code goes in a standard code module:

Option Explicit

Sub Pivots_SwitchFields(target As PivotTable)
Dim rngSelection As Range
Dim wks As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim lngOrientation As Long
Dim varFields As Variant
Dim varItem As Variant
Dim strInstructions As String
Dim lCalculation As Long
Dim bEnableEvents As Boolean
Dim bScreenUpdating As Boolean


With Application
    lCalculation = .Calculation
    bEnableEvents = .EnableEvents
    bScreenUpdating = .ScreenUpdating
    .EnableEvents = False
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With


strInstructions = target.Name
Set rngSelection = target.RowRange
Set wks = ActiveWorkbook.Worksheets(Split(strInstructions, "|")(1))
Set pt = wks.PivotTables(Split(strInstructions, "|")(2))
pt.ManualUpdate = True

Select Case Split(strInstructions, "|")(3)
Case "Values": lngOrientation = xlDataField
Case "Rows": lngOrientation = xlRowField
Case "Columns": lngOrientation = xlColumnField
Case "Filters": lngOrientation = xlPageField
End Select

'Clear out the old field(s)
Select Case lngOrientation
    Case xlDataField
        For Each pf In pt.DataFields
            pf.Orientation = xlHidden
        Next pf

    Case Else
        For Each pf In pt.PivotFields
            With pf
                If .Orientation = lngOrientation Then
                    If Not .AllItemsVisible Then .ClearAllFilters
                    .Orientation = xlHidden
                End If
            End With
        Next pf
End Select

'Add in the new field(s)
varFields = target.RowRange
On Error Resume Next
For Each varItem In varFields
    With pt.PivotFields(varItem)
    .Orientation = lngOrientation
    If lngOrientation = xlDataField Then .Name = .SourceName & " "
End With

Next varItem
On Error GoTo 0

With Application
    .EnableEvents = bEnableEvents
    .ScreenUpdating = bScreenUpdating
    .Calculation = lCalculation
End With
pt.ManualUpdate = False

End Sub

...and this code goes in the ThisWorkbook module:

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal target As PivotTable)
If Split(target.Name, "|")(0) = "SwitchFields" Then Pivots_SwitchFields target
End Sub

Note that I've given the Hidden Pivot the following name: SwitchFields|Sheet1|PivotTable1|Values ...where that | character is the pipe character you get by pressing Shift and \ together.

You will need to amend this name to provide the relevant sheet name, PivotTable name, and location of the fields you want to switch out (i.e. Values, Rows, Columns, Filters)

Another option is to use the code I posted at http://dailydoseofexcel.com/archives/2013/11/21/unpivot-shootout/ to turn the crosstab directly into a PivotTable using some SQL. Look for the very long routine I posted below the heading "Update 26 November 2013" in that article. That routine will turn the crosstab to a flat file if it fits in the sheet, and otherwise turn it directly into a PivotTable via SQL (although it might take a couple of minutes to do so).

Converting multiple variables into values with excel pivot tables or power pivot

1 Answers