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!

 

[arun_eagertolearn]

Hi

In the filter text field (Search field), when you type "Blue" after having typed "Chicos", there would be an option appearing at the top of the filter- "Add selection to the current filter". Selecting that would keep both options

 

[frewert]

Hi

In the filter text field (Search field), when you type "Blue" after having typed "Chicos", there would be an option appearing at the top of the filter- "Add selection to the current filter". Selecting that would keep both options

When I do that, the first selection goes away.

1.) type Chicos
2.) press enter
3.) type blue
4.) press add to selection
5.) press enter
6.) Chicos goes away.

 

[arun_eagertolearn]

In Step 5, please press Space instead of Enter

 

[frewert]

In Step 5, please press Space instead of Enter

The Space only checks or unchecks Add selection to the current filter

In both cases, typing in "blue" will select all listings with "blue" in it. Instead of only selecting listings with Chicos AND blue

 

[arun_eagertolearn]

Sorry, my understanding had been wrong

I had interpreted it to be chicos OR blue

Please find a VBA code below, which would be of help to select fields separated by a space (say chicos AND blue)

Please change field1, marked as 'Colour' here appropriately to reflect the actual column which you need to filter

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 = "Colour" 'Please change appropriately
    
    Set pf = pt.PivotFields(field1)
                
    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
        pi.Visible = True
        
        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 tried your code but it ended up looping continuously. Maybe I have entered the wrong thing for "colour".

The Pivot Table field to search is called "Item Title"

This is what I replaced "colour" with in the code.

Was I supposed to use "Slicer_Item_Title" instead?

Another thing to point out. I have made a duplicate Pivot Table that is hidden. This copy of PivotTable1 is giving me the ability to do a search filter. The technique I learned from a Youtube tutorial,

I removed the second pivot table and tried again. Still the looping behavior is present.

 

[arun_eagertolearn]

Just a small modification to the code

Please check this; it is working well for me

Changed field 1 to "Item Label" (heading in the source data column)

This looks at the first pivot table in a given sheet

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 Label" 'Please change appropriately
    
    Set pf = pt.PivotFields(field1)
    
    For Each pi In pf.PivotItems
        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 get, Error 400

I don't see where "Item Label" is found. My source data table is as follows:

Item Title	Sold For	Shipping	Sale Date	Days Til Sold	Rate	Size	Category	Gender	Condition
Maeve Athropologie Sweater Poncho Womens One Size Turtleneck Sleeveless	32.98	7	6/13/2024	52	173%	One Size	Sweaters	Womens	Pre-owned
Old Navy Denim Shirt Womens 4X Pockets Cotton Blue Long Sleeve Button Up	22.48	8.6	6/13/2024	22	409%	4X	Tops	Womens	Pre-owned
Fabletics The Only Pant Classic Fit Mens 42X28 Purple Polyester Spandex Zip	24.98	7	6/13/2024	211	43%	42	Pants	Mens	Pre-owned
Lacoste Button Front Shirt Mens 42 Large L Cotton Long Sleeve Purple Pocket	13.94	7	6/13/2024	36	250%	L	Tops	Mens	Pre-owned
Foxxy T-Shirt Dress Womens 2X Black White Stripe Rayon Stretch Short Sleeve	6.78	7	6/13/2024	322	28%	2X	Tops	Womens	Pre-owned
Forever 21 NWT 2" Denim Shorts Mid Rise Stretch Blue Womens 25	1.68	5.5	6/12/2024	695	13%	25	Shorts	Womens	New
Polo Ralph Lauren Dress Shirt Philip Mens 16 32/33 Blue Striped Cotton LS	9.44	5.5	6/12/2024	70	129%	33	Tops	Mens	Pre-owned
Alfani NWT T-Shirt End on End Tee Stretch Red V-Neck Short Sleeve Tech Mens M	8.48	5.5	6/12/2024	476	19%	M	Tops	Mens	New
Orvis Pants Womens Large Pull On 100% Linen Brown Drawstring Elastic Waist	15.28	7	6/12/2024	197	46%	L	Pants	Womens	Pre-owned
Christian Siriano New York Dress Womens 2XL XXL Teal Snake Print Shift Stretch V	11.68	7	6/12/2024	107	84%	2XL	Dresses	Womens	Pre-owned
Tommy Bahama Silk Shirt Mens Large Pink Plaid Short Sleeve Button Front	9.98	5.5	6/11/2024	91	99%	L	Tops	Mens	Pre-owned
Brooks Brothers Polo Shirt Mens XL Long Sleeve Cotton Original Fit Red	19.98	7	6/11/2024	28	321%	XL	Tops	Mens	Pre-owned
Essentials By Maggie Barnes Button Front Shirt Womens 32/32 4X Cotton Plaid	13.94	7	6/10/2024	83	108%	4X	Tops	Womens	Pre-owned
Knox Rose NWT Target Blouse Top Shirt Blue Sleeveless Rayon Womens M	11.68	5.5	6/10/2024	474	19%	M	Tops	Womens	New
Chicos Dress Womens Size 3 (US XL) Blue Green Teal Paisley 3/4 Sleeve Stretch	26.98	7	6/10/2024	117	77%	XL	Dresses	Womens	Pre-owned
Ruby Rd Blouse Womens 2X Rayon Spandex Beige Tank Top With Sheer Sleeves	11.68	7	6/9/2024	107	84%	2X	Tops	Womens	Pre-owned
Kirkland 100% Cashmere Sweater Polo Mens XL Brown Cable Knit Signature	41.98	7	6/9/2024	23	391%	XL	Sweaters	Mens	Pre-owned
LOFT Ann Taylor NWT Skirt Womens 10 Cotton A-Line Pleated Side Zip Short	9.18		6/9/2024	194	46%	10	Shorts	Womens	New
Ann Taylor NWT Dress Womens Large Black/White Fit Flare Sleeveless Stretch	19.29		6/9/2024	221	41%	L	Dresses	Womens	New
American Eagle Curvy Super Hi Rise Shortie Blue Denim Shorts Womens 8	8.98	7	6/9/2024	710	13%	8	Shorts	Womens	Pre-owned
Diana Lyne USA T-Shirt Womens 2X Cotton Polyester Purple Short Sleeve	13.48	7	6/8/2024	23	391%	2X	Tops	Womens	Pre-owned
Coldwater Creek Blouse Womens Large 14/16 Silk Relaxed Short Sleeve Pocket	11.68	5.5	6/8/2024	164	55%	L	Tops	Womens	Pre-owned
Alfred Dunner Blouse Womens Large Lace Short Sleeve Blue Green Lined Knot Front	9.88	5.5	6/8/2024	180	50%	L	Tops	Womens	Pre-owned
Under Armour T-Shirt Mens XL Blue Polyester Short Sleeve Crew Logo HeatGear	8.98	5.5	6/8/2024	106	85%	XL	Tops	Mens	Pre-owned
Lucky Brand Blouse Womens 2X White Floral Pullover Rayon Long Sleeve Tie	19.98	5.5	6/8/2024	172	52%	2X	Tops	Womens	Pre-owned

Here is a link to the actual workbook, My Sold Research Tool v3.xlsm

 

[Joe4]

I don't see where "Item Label" is found. My source data table is as follows:

If you look at the code he posted, he has this line:

    field1 = "Item Label" 'Please change appropriately

The "Please change appropriately" is a cue to you to change the value to match your data!

Based on your image, it looks like you will want to change that to:

    field1 = "Item Title" 'Please change appropriately