75609

Select all Pivot Items that match list of items in another Excel sheet

Question:

I have one Excel sheet that contains a list of people and their associated departments. In a PivotTable on another sheet, I would like to filter my results so that all items "Assigned To" any of the people in a given department will be displayed.

So far, I have code that will filter the list of people to the desired department, and will create an array that contains the names of all of those people. I have then tried to filter the PivotItems that contain these lists of names to be visible and all others to be hidden, but when I try to run the macro it is just continuously thinking. Is there an easier way to do this?

ActiveSheet.Range("$A$1:$E$175").AutoFilter Field:=4, Criteria1:= _ "DEPARTMENT NAME" 'Selects first visible row of filtered data set & _ create array that contains all filtered names ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(3, 2).Select employeerange = "C" & ActiveCell.Row & ":C" & ActiveSheet.Rows.Count Dim employeearray As Variant employeearray = Range(employeerange).Value 'Cycle through all possible items for the given Pivot Field and compare to _ each of the names in the employee array. Set items that match to visible _ and all others to hidden. Dim PI As PivotItem Dim element As Variant With ActiveSheet.PivotTables("PivotTable2").PivotFields("PIVOT FIELD") For Each PI In .PivotItems For Each element In employeearray If PI Like "*" & CStr(element) & "*" Then PI.Visible = True Else PI.Visible = False End If Next element Next PI End With

Answer1:

Have you checked the first part of your code that builds the array? I can't see your data but I'm not sure that will work the way you hope.

To build up your array, use something like:

ActiveSheet.Range("$A$1:$E$175").AutoFilter Field:=4, Criteria1:= "DEPARTMENT NAME" Dim employeearray As Variant employeearray = ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Columns("C").Value

As for the 2nd part of the macro..

You're over-testing the match, which if you left it to run would hide everything but the last match.

Use the following instead, which loops through all the .PivotItems but uses a single test to check if the item is in the array.

Dim PI As PivotItem With ActiveSheet.PivotTables("PivotTable2").PivotFields("PIVOT FIELD") For Each PI In .PivotItems PI.Visible = (UBound(Filter(employeearray, PI.Name)) > -1) Next PI End With

Answer2:

When iterating over PivotItems, there's a couple of bottlenecks and gotchas that you want to avoid. See my post at <a href="http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/" rel="nofollow">http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/</a> for more on this.

Among other things, you want to set the PivotTable's ManualUpdate property to TRUE while you do the iteration and then back to FALSE when you're done. Otherwise Excel will try to update the PivotTable each time you change the visibility of a PivotItem. And you also want to ensure that at least one item remains visible at all times. I use something like this:

Option Explicit Sub FilterPivot() Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem Dim i As Long Dim vItem As Variant Dim vCountries As Variant Set pt = ActiveSheet.PivotTables("PivotTable1") Set pf = pt.PivotFields("CountryName") vCountries = Array("FRANCE", "BELGIUM", "LUXEMBOURG") pt.ManualUpdate = True 'Stops PivotTable from refreshing after each PivotItem is changed With pf 'At least one item must remain visible in the PivotTable at all times, so make the first 'item visible, and at the end of the routine, check if it actually *should* be visible .PivotItems(1).Visible = True 'Hide any other items that aren't already hidden. 'Note that it is far quicker to check the status than to change it. ' So only hide each item if it isn't already hidden For i = 2 To .PivotItems.Count If .PivotItems(i).Visible Then .PivotItems(i).Visible = False Next i 'Make the PivotItems of interest visible On Error Resume Next 'In case one of the items isn't found For Each vItem In vCountries .PivotItems(vItem).Visible = True Next vItem On Error GoTo 0 'Hide the first PivotItem, unless it is one of the countries of interest On Error Resume Next If InStr(UCase(Join(vCountries, "|")), UCase(.PivotItems(1))) = 0 Then .PivotItems(1).Visible = False If Err.Number <> 0 Then .ClearAllFilters MsgBox Title:="No Items Found", Prompt:="None of the desired items was found in the Pivot, so I have cleared the filter" End If On Error GoTo 0 End With pt.ManualUpdate = False End Sub

Recommend

  • Retrieve List of Cells in Excel Where Column Equals Something
  • SQL Server PIVOT Function
  • Symfony2 Form is always empty after submitting
  • uninitialized AudioTrack exception when I try to generate tone on 22nd time
  • how to pivot complex dataframe
  • Single Table Inheritance in Rails 4
  • Why does an exact match on a name return a useless set of venues?
  • AlarmManager is blocking main thread
  • Product and product template in Odoo 10
  • How to change a windows path to a linux path in all files under a directory using sed
  • Number of ways to partition a number in Python
  • Spree, Ruby on Rails - Add to cart multiple variants of the same product
  • How can I use a custom function within an expression using the eval dataframe method?
  • Two columns in subquery in where clause
  • Shopify API CARTS - Changing line_item line_price for price Override
  • I don't get what's the difference between format() and … (python)
  • Build Matrix of Comparisons in SQl Server
  • Express displaying mongodb documents in Jade
  • Comparing user's facebook/twitter friends to site's users in Python/Django
  • Not able to display correct data in table -AngularJS
  • runtime-check whether an instance (Base*) override a parent function (Base::f())
  • Creating a Multidimensional, Associative Array in VBScript
  • Where can I find tomesh.c?
  • JavaScript IE rotation transform maths
  • Conversion from string “a” to type 'Boolean' is not valid
  • C: Incompatible pointer type initializing
  • why xml file does not aligned properly after append the string in beginning and end of the file usin
  • preg_replace Double Spaces to tab (\\t) at the beginning of a line
  • Extracting HTML between tags
  • MongoDB in PHP using aggregate to group by _id is null not working
  • Why HTML5 Canvas with a larger size stretch a drawn line?
  • Weird JavaScript statement, what does it mean?
  • RestKit - RKRequestDelegate does not exist
  • AngularJs get employee from factory
  • How to set the response of a form post action to a iframe source?
  • Change div Background jquery
  • Qt: Run a script BEFORE make
  • python draw pie shapes with colour filled
  • Django query for large number of relationships
  • reshape alternating columns in less time and using less memory