Pivot table search filter without exact match?

frewert

Board Regular
Joined
Apr 4, 2014
Messages
188
Office Version
  1. 365
Platform
  1. Windows
Can something be done to change the way a search filter works inside a pivot table?

If I search "Chicos blue" I want it to return all cells with "Chicos" and "blue", not just the "Chicos blue" exact match.

In other words, if I separate my search filter queries by space, the space would act like the AND operator.

1718567486777.png

Thanks!
 
Changing it back to "Item Title" I get looping behavior (a forever loading icon as a mouse cursor). Pressing escape and then debug, I get this:
1718733123255.png
 
Upvote 0

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Changing it back to "Item Title" I get looping behavior (a forever loading icon as a mouse cursor). Pressing escape and then debug, I get this:
I do not work much with Pivot Tables, especially not in VBA, so I will have to defer to the writer of the code, arun_eargertolearn, on that!
 
Upvote 0
Hi

Can you please let know how many entries are there beneath the pivot field 'Item Title'

The section highlighted first gets the pivot to display all entries

The time taken may be due to large number of entries

Just to check, can you please do the following

Ensure all entries below 'Item Title' are selected
Remove this bit of code

VBA Code:
For Each pi In pf.PivotItems
        pi.Visible = True
    Next pi
 
Last edited:
Upvote 0
Specifically, please use this code, with all the entries below 'Item Title' selected manually in the pivot table

VBA Code:
Sub pivot_table_select()

    Dim input1 As String
    Dim input2 As String
    Dim field1 As String
    Dim ws As Worksheet
    
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    
    Dim store_array(0 To 10) As String
    Dim inp_array() As String
    Dim i, j As Integer
          
    
    For i = LBound(store_array) To UBound(store_array)
        store_array(i) = ""
    Next i
                
    Set ws = ThisWorkbook.ActiveSheet
    If ws.PivotTables.Count > 0 Then
        Set pt = ws.PivotTables(1)
    Else
        MsgBox ("No pivot table")
            Exit Sub
    End If
    
    field1 = "Item Title" 'Please change appropriately
    
    Set pf = pt.PivotFields(field1)
    
    'For Each pi In pf.PivotItems   (these have been commented out, they wont execute)
    '    pi.Visible = True
    'Next pi
                
    inp = InputBox("Please enter")
    
    If InStr(1, inp, " ", vbTextCompare) > 0 Then
        ReDim inp_array(0 To InStr(1, inp, " ", vbTextCompare))
        inp_array = Split(inp, " ")
    Else
        ReDim inp_array(0 To 0)
        inp_array(0) = inp
    End If
    
    inp_count = 0
        
    For i = LBound(inp_array) To UBound(inp_array)
        store_array(i) = inp_array(i)
        inp_count = inp_count + 1
    Next i
            
    For Each pi In pf.PivotItems
               
        For j = 0 To inp_count - 1
            If InStr(1, pi.Name, store_array(j), vbTextCompare) = 0 Then
                pi.Visible = False
                Exit For
            End If
        Next j
        
    Next pi

End Sub
 
Upvote 0
I have manually selected this area (attached picture). Though it may be incorrect because technically I do not see a column in my pivot table that says Item Title.

No Error 400. The prompt comes up asking me to type in my query. But then after I press enter it will go into a constant loop.

There are about 5000 items in the column.
 

Attachments

  • Untitled.png
    Untitled.png
    201.1 KB · Views: 4
Upvote 0
Thanks, I feel the issue is due to the size and the number of entries
It may take time to go through 5000 + entries

I have just introduced a counter in column G, which looks vacant
This is to assure that the execution is progressing

Reduced the number of separate entries (eg: Chicos, Blue) to 2, so as to quicken the loop executions

As long as the source data column is named 'Item Title' (as visible in the Pivot Fields), it is the entry we will have to target


VBA Code:
Sub pivot_table_select()

    Dim input1 As String
    Dim input2 As String
    Dim field1 As String
    Dim ws As Worksheet
    
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    
    Dim store_array(0 To 1) As String ' 2 entries eg: chicos blue
    Dim inp_array() As String
    Dim i, j As Integer
    Dim pf_counter As Integer
    Dim v_counter As Integer
    
  
    For i = LBound(store_array) To UBound(store_array)
        store_array(i) = ""
    Next i
                
    Set ws = ThisWorkbook.ActiveSheet
    If ws.PivotTables.Count > 0 Then
        Set pt = ws.PivotTables(1)
    Else
        MsgBox ("No pivot table")
            Exit Sub
    End If
    
    field1 = "Item Title" 'Please change appropriately
    
    Set pf = pt.PivotFields(field1)
    
    
    'For Each pi In pf.PivotItems
    '   pi.Visible = True
    'Next pi
                
    inp = InputBox("Please enter") 'Separate multiple strings with a space
    
    If InStr(1, inp, " ", vbTextCompare) > 0 Then
        ReDim inp_array(0 To InStr(1, inp, " ", vbTextCompare))
        inp_array = Split(inp, " ")
    Else
        ReDim inp_array(0 To 0)
        inp_array(0) = inp
    End If
    
    inp_count = 0
        
    For i = LBound(inp_array) To UBound(inp_array)
        store_array(i) = inp_array(i)
        inp_count = inp_count + 1
        Debug.Print inp_count
    Next i
            
    pf_count = 1
    
    For Each pi In pf.PivotItems
        pf_count = pf_count + 1
        For j = 0 To inp_count - 1
            If InStr(1, pi.Name, store_array(j), vbTextCompare) = 0 Then
                pi.Visible = False
                Exit For
            End If
            
        Next j
        
        If pf_count Mod 100 = 0 Then
            ActiveSheet.Cells(Int(pf_count / 100), 7) = pf_count ' to keep a count of the number of cells it is going through
        End If
        
    Next pi

End Sub
 
Upvote 0
Ok, it seems like the problem is the speed. The counter shows 100, 200, and so on. But it is just very slow. Maybe 30 seconds to reach 100.

Thanks for your efforts thus far.
 
Upvote 0
Ok, it seems like the problem is the speed. The counter shows 100, 200, and so on. But it is just very slow. Maybe 30 seconds to reach 100.

Thanks for your efforts thus far.
See if this helps speed up the code at all (note the additions in red):
Rich (BB code):
Sub pivot_table_select()

    Dim input1 As String
    Dim input2 As String
    Dim field1 As String
    Dim ws As Worksheet
    
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    
    Dim store_array(0 To 1) As String ' 2 entries eg: chicos blue
    Dim inp_array() As String
    Dim i, j As Integer
    Dim pf_counter As Integer
    Dim v_counter As Integer
    
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
  
    For i = LBound(store_array) To UBound(store_array)
        store_array(i) = ""
    Next i
                
    Set ws = ThisWorkbook.ActiveSheet
    If ws.PivotTables.Count > 0 Then
        Set pt = ws.PivotTables(1)
    Else
        MsgBox ("No pivot table")
            Exit Sub
    End If
    
    field1 = "Item Title" 'Please change appropriately
    
    Set pf = pt.PivotFields(field1)
    
    
    'For Each pi In pf.PivotItems
    '   pi.Visible = True
    'Next pi
                
    inp = InputBox("Please enter") 'Separate multiple strings with a space
    
    If InStr(1, inp, " ", vbTextCompare) > 0 Then
        ReDim inp_array(0 To InStr(1, inp, " ", vbTextCompare))
        inp_array = Split(inp, " ")
    Else
        ReDim inp_array(0 To 0)
        inp_array(0) = inp
    End If
    
    inp_count = 0
        
    For i = LBound(inp_array) To UBound(inp_array)
        store_array(i) = inp_array(i)
        inp_count = inp_count + 1
        Debug.Print inp_count
    Next i
            
    pf_count = 1
    
    For Each pi In pf.PivotItems
        pf_count = pf_count + 1
        For j = 0 To inp_count - 1
            If InStr(1, pi.Name, store_array(j), vbTextCompare) = 0 Then
                pi.Visible = False
                Exit For
            End If
            
        Next j
        
        If pf_count Mod 100 = 0 Then
            ActiveSheet.Cells(Int(pf_count / 100), 7) = pf_count ' to keep a count of the number of cells it is going through
        End If
        
    Next pi

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub
 
Upvote 0
How about an alternate approach ?

Does this give you any ideas ?
(I used 2 columns but it doesn't need to be)

My Sold Research Tool v3.xlsm
ABCDEFGHIJKLMNO
1Item TitleSold ForShippingSale DateDays Til SoldRateSizeCategoryGenderConditionSearch TermSelect YSearch -->Cotton Long
2Maeve Athropologie Sweater Poncho Womens One Size Turtleneck Sleeveless32.98713/06/202452173%One SizeSweatersWomensPre-owned  
3Old Navy Denim Shirt Womens 4X Pockets Cotton Blue Long Sleeve Button Up22.488.613/06/202422409%4XTopsWomensPre-ownedCotton LongYSearch Term List
4Fabletics The Only Pant Classic Fit Mens 42X28 Purple Polyester Spandex Zip24.98713/06/202421143%42PantsMensPre-owned  Cotton
5Lacoste Button Front Shirt Mens 42 Large L Cotton Long Sleeve Purple Pocket13.94713/06/202436250%LTopsMensPre-ownedCotton LongYLong
6Foxxy T-Shirt Dress Womens 2X Black White Stripe Rayon Stretch Short Sleeve6.78713/06/202432228%2XTopsWomensPre-owned  
7Forever 21 NWT 2" Denim Shorts Mid Rise Stretch Blue Womens 251.685.512/06/202469513%25ShortsWomensNew  
8Polo Ralph Lauren Dress Shirt Philip Mens 16 32/33 Blue Striped Cotton LS9.445.512/06/202470129%33TopsMensPre-owned  
9Alfani NWT T-Shirt End on End Tee Stretch Red V-Neck Short Sleeve Tech Mens M8.485.512/06/202447619%MTopsMensNew  
10Orvis Pants Womens Large Pull On 100% Linen Brown Drawstring Elastic Waist15.28712/06/202419746%LPantsWomensPre-owned  
11Christian Siriano New York Dress Womens 2XL XXL Teal Snake Print Shift Stretch V11.68712/06/202410784%2XLDressesWomensPre-owned  
12Tommy Bahama Silk Shirt Mens Large Pink Plaid Short Sleeve Button Front9.985.511/06/20249199%LTopsMensPre-owned  
13Brooks Brothers Polo Shirt Mens XL Long Sleeve Cotton Original Fit Red19.98711/06/202428321%XLTopsMensPre-ownedCotton LongY
14Essentials By Maggie Barnes Button Front Shirt Womens 32/32 4X Cotton Plaid13.94710/06/202483108%4XTopsWomensPre-owned  
15Knox Rose NWT Target Blouse Top Shirt Blue Sleeveless Rayon Womens M11.685.510/06/202447419%MTopsWomensNew  
eBay CSV files
Cell Formulas
RangeFormula
K2:K15K2=IF(SUM(IF(ISNUMBER(SEARCH(N$4#,[@[Item Title]])),1,0))=COUNTA(N$4#),O$1,"")
L2:L15L2=IF([@[Search Term]]<>"","Y","")
N4:N5N4=TRANSPOSE(TEXTSPLIT(O1," "))
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top