Pivot table: show only data for which a field does not contain a value

[droopy]

Hello,
I don't know if it's because it's Monday, but I can't find a way to do something which I feel should be very basic.

I have a data table (containing survey results) which I base many pivots on.
In one of the pivots, I need to compute / calculate averages or counts of responses only when companies do not contain some combination of characters (e.g. company does not contain "abc" with combination of dash (-) or space and upper / lower case).

Example:
If I have 4 responses (values: 5, 5, 4, 3 and companies: "OneCompany", "A bc inc", "Another Company", "Rehab-c"), then I expect my pivot data showing average of responses to output 4.5 (average of values for responses 1 and 3), if I am able to filter out as described above.

Company is part of the raw data, but is not shown in the pivot table, I just want the pivot table filtered on company.

Whether I try to put a slicer or a filter on that company field, I cannot find a way to make a filter rule.
I'm just able to manually select what companies I want to include in pivot calculation, which is not practical at all: each time my dataset changes and a new company appears, I have to parse again the list and check or uncheck companies.

I know how to use filter labels, but this works if the filtered field is in the pivot report - which is not my case.

Is what I'm looking for impossible, or am I just dumb this morning?

Any help welcome!

 

[PeterB]

Hi Droopy,

Will you accept a VBA solution?

Thanks,

Peter

 

[droopy]

Hi Peter,

Will you accept a VBA solution?

I'd almost have preferred to read that I'm dumb today

I can live with VBA if there is no other option. Otherwise if powerpivot offers a solution, that could be even better (I'm on 2013, and already use powerpivot for calculating moving averages and so on).

 

[PeterB]

Hi Droopy,

I'm afraid I don't have PowerPivot so I can't help you with that. I have however drafted a VBA solution for you and attached a workbook with sample data.

Sub FilterPivotField()

Dim pf As PivotField
Dim pi As PivotItem
Dim sName As String

Set pf = ActiveSheet.PivotTables("PivotTable1").PivotFields("Company")

'Loop all items in the 'Company' field
For Each pi In pf.PivotItems

  'Start by showing the item
  pi.Visible = True

  'Various string replace functions to remove spaces and dashes
  'We also conver to lowercase as the search is not case sensitive
  sName = Replace$(pi.Name, " ", vbNullString)
  sName = Replace$(sName, "-", vbNullString)
  sName = LCase$(sName)

  'If the PivotItem contains the search string then hide it
  If InStr(sName, LCase("one")) > 0 Then
    pi.Visible = False
  End If

Next pi

'Clean up
Set pi = Nothing
Set pf = Nothing
   
End Sub

Regards,

Peter

 

[droopy]

Hi Peter,

Sorry for the late answer, I got preempted on something (more) urgent.
Thank you for your response.

While your solution also satisfies my need, I finally managed to solve the issue by adding a (boolean) calculated column in Powerpivot, which is easier to maintain than VBA code.
I probably just needed to reconnect synapses...
I already have enough VBA code in that workbook .

Thank you again for you help and time!
Kind Regards.