Pivot table search filter without exact match?

[frewert]

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.

Thanks!

 

[frewert]

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:

 

[Joe4]

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!

 

[arun_eagertolearn]

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

For Each pi In pf.PivotItems
        pi.Visible = True
    Next pi

 

[arun_eagertolearn]

Specifically, please use this code, with all the entries below 'Item Title' selected manually in the pivot table

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

 

[frewert]

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.

 

[arun_eagertolearn]

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

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

 

[frewert]

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.

 

[Joe4]

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):

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

 

[frewert]

See if this helps speed up the code at all (note the additions in red):

No change in speed with that revision.

 

[Alex Blakenburg]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Item Title	Sold For	Shipping	Sale Date	Days Til Sold	Rate	Size	Category	Gender	Condition	Search Term	Select Y		Search -->	Cotton Long
2	Maeve Athropologie Sweater Poncho Womens One Size Turtleneck Sleeveless	32.98	7	13/06/2024	52	173%	One Size	Sweaters	Womens	Pre-owned
3	Old Navy Denim Shirt Womens 4X Pockets Cotton Blue Long Sleeve Button Up	22.48	8.6	13/06/2024	22	409%	4X	Tops	Womens	Pre-owned	Cotton Long	Y		Search Term List
4	Fabletics The Only Pant Classic Fit Mens 42X28 Purple Polyester Spandex Zip	24.98	7	13/06/2024	211	43%	42	Pants	Mens	Pre-owned				Cotton
5	Lacoste Button Front Shirt Mens 42 Large L Cotton Long Sleeve Purple Pocket	13.94	7	13/06/2024	36	250%	L	Tops	Mens	Pre-owned	Cotton Long	Y		Long
6	Foxxy T-Shirt Dress Womens 2X Black White Stripe Rayon Stretch Short Sleeve	6.78	7	13/06/2024	322	28%	2X	Tops	Womens	Pre-owned
7	Forever 21 NWT 2" Denim Shorts Mid Rise Stretch Blue Womens 25	1.68	5.5	12/06/2024	695	13%	25	Shorts	Womens	New
8	Polo Ralph Lauren Dress Shirt Philip Mens 16 32/33 Blue Striped Cotton LS	9.44	5.5	12/06/2024	70	129%	33	Tops	Mens	Pre-owned
9	Alfani NWT T-Shirt End on End Tee Stretch Red V-Neck Short Sleeve Tech Mens M	8.48	5.5	12/06/2024	476	19%	M	Tops	Mens	New
10	Orvis Pants Womens Large Pull On 100% Linen Brown Drawstring Elastic Waist	15.28	7	12/06/2024	197	46%	L	Pants	Womens	Pre-owned
11	Christian Siriano New York Dress Womens 2XL XXL Teal Snake Print Shift Stretch V	11.68	7	12/06/2024	107	84%	2XL	Dresses	Womens	Pre-owned
12	Tommy Bahama Silk Shirt Mens Large Pink Plaid Short Sleeve Button Front	9.98	5.5	11/06/2024	91	99%	L	Tops	Mens	Pre-owned
13	Brooks Brothers Polo Shirt Mens XL Long Sleeve Cotton Original Fit Red	19.98	7	11/06/2024	28	321%	XL	Tops	Mens	Pre-owned	Cotton Long	Y
14	Essentials By Maggie Barnes Button Front Shirt Womens 32/32 4X Cotton Plaid	13.94	7	10/06/2024	83	108%	4X	Tops	Womens	Pre-owned
15	Knox Rose NWT Target Blouse Top Shirt Blue Sleeveless Rayon Womens M	11.68	5.5	10/06/2024	474	19%	M	Tops	Womens	New
eBay CSV files

Cell Formulas
Range	Formula
K2:K15	K2	=IF(SUM(IF(ISNUMBER(SEARCH(N$4#,[@[Item Title]])),1,0))=COUNTA(N$4#),O$1,"")
L2:L15	L2	=IF([@[Search Term]]<>"","Y","")
N4:N5	N4	=TRANSPOSE(TEXTSPLIT(O1," "))
Dynamic array formulas.