0

I have a huge amount of data (200k+ rows and 10+ columns), compiled in this format:

Month    Date        Time          Value1    Value2    Value3
Jan-18   1/1/2018    12:00:00 AM     10        20        30
Jan-18   1/1/2018    12:01:00 AM     13        23        33
etc...

I've used PivotTables and PivotCharts with slicers to allow my charts to sort the data according to months. However, I want to be able to choose which column to show, for example: slicers which allow me to show Value1 or Value2 depending on my choice.

A method which worked for me was to compile the data in this manner:

Month    Date        Time          Type    Value
Jan-18   1/1/2018    12:00:00 AM    1       10 
Jan-18   1/1/2018    12:01:00 AM    1       13  
Jan-18   1/1/2018    12:00:00 AM    2       20 
Jan-18   1/1/2018    12:01:00 AM    2       23

This allowed me to sort with slicers according to type. This is not a very viable option as data is constantly added and is just a pain to do so.

Is there a better way to do this? I've searched around for this but could not find a solution, would appreciate any input.

Cheers!

Kevin Lee
  • 27
  • 1
  • 8

1 Answers1

1

Yup, you can use the VBA I've previously posted at Converting multiple variables into values with excel pivot tables or power pivot to do that. It requires you to make up a 'harvester' PivotTable containing just the columns you want to swap out, so that you can then create a slicer from which to trigger the code. But I'm pretty sure this will let you do exactly what you want.

jeffreyweir
  • 4,668
  • 1
  • 16
  • 27
  • thanks for the response! I've went through the steps but I don't quite understand how to achieve it. Am very new to VBA. I made the macros following your code but it doesn't show on the macro menu on excel when I try to run it – Kevin Lee Jul 17 '18 at 01:41
  • The first sub called sub called `Pivots_SwitchFields` goes in a standard code module. The second sub called `Workbook_SheetPivotTableUpdate`goes in the ThisWorkbook module, **not** a standard code module. That second sub is an event handler, that 'listens' out for changes to the slicer, and then runs the first sub automatically. Nothing shows up in the macro menu, because you don't need to run the subs manually. – jeffreyweir Jul 17 '18 at 08:15
  • I have paste the code accordingly, but received an error: "Runtime error '1004': Method 'PivotTables' of object '_Worksheet' failed" at this line of code: `Set pt = wks.PivotTables(Split(strInstructions, "|")(2))`. I have used the same example as you did, with Sheet1 (data source), Sheet2(PivotTable1 from source), Sheet3(Hidden Pivot source) and Sheet4(Hidden Pivot table with SwitchFields|Sheet1|PivotTable1|Values as the PivotTable name) – Kevin Lee Jul 18 '18 at 00:46
  • Okay.Can you run the code again, and then in the immediate window type ? strInstructions and push Enter and tell me the output, and then do the same with ? wks.name too. If you can't see a window called the Immediate Window in the VBA editor then select View > Immediate Window from the options across the top. – jeffreyweir Jul 18 '18 at 07:45
  • ?strInstructions gave: SwitchFields|Sheet1|PivotTable1|Values, and ?wks.name gave an error: Object variance or With block variable not set. My PivotTable1 (from source) doesn't have "Values" in it, could it be why this is causing the error? If this is the problem, how do you group columns in a PivotTable such as in your example in the link? – Kevin Lee Jul 18 '18 at 08:15
  • Can you select a cell in your PIvotTable and type this in the immediate window: ? activecell.PivotTable.parent.name – jeffreyweir Jul 18 '18 at 08:24
  • output was Sheet 2 – Kevin Lee Jul 18 '18 at 08:35
  • There's the issue. The hidden pivot's name should then be SwitchFields|Sheet2|PivotTable1|Values instead of SwitchFields|Sheet1|PivotTable1|Values. That "SheetX" bit tells the code where the 'slave' pivot is. – jeffreyweir Jul 18 '18 at 08:46
  • i.e. you need the actual sheet name in there, not the sheet code name. You need to name the hidden pivot like so: SwitchFields|WorksheetName|PivotTableName|Values ...where that | character is the pipe character you get by pressing Shift and \ together. The "Values" bit tells the code which fields you want to switch out (i.e. Values, Rows, Columns, Filters). In your case, I think the fields you want to switch are in the Values area, so this should be "Values" – jeffreyweir Jul 18 '18 at 08:50
  • No luck unfortunately, changed the name of the HiddenPivot accordingly: SwitchFields|Sheet2|PivotTable1|Values, but got an error at `Set rngSelection = target.RowRange` (Unable to get RowRange property of PivotTable class). ? strInstructions gave the hidden pivot table name, and ? wks.Name same error as before. Where did you create your hidden pivot? – Kevin Lee Jul 18 '18 at 09:02
  • Shouldn't matter where the hidden pivot is. So you've created the hidden pivot and put the field in it as a row field? – jeffreyweir Jul 18 '18 at 09:05
  • Yup, so now i have 4 sheets, Sheet1: Data source, Sheet2: PivotTable from data source, Sheet3: Hidden Pivot data source (just a table with Weeks as header, Week1, Week2...), Sheet4: Hidden Pivot with the correct name, with the Weeks field in rows. – Kevin Lee Jul 18 '18 at 09:14
  • When you get that error, what does ? Target.name return – jeffreyweir Jul 18 '18 at 09:16
  • OK it worked somehow, had to make a brand new workbook to test it out. I guess it was naming the hidden pivot that was the main culprit. Also seems like once it hits an error it doesn't work, even after changing the hidden pivot to the correct name. Thank you so much for your help, you've been amazing. Cheers! – Kevin Lee Jul 18 '18 at 09:25
  • Cool, glad we got there in the end. – jeffreyweir Jul 18 '18 at 09:28