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

Just rewrote the code a bit
Here, rather than having to iterate twice over the entire pivot items, this would restrict the count based on the first input (say "Chicos") and iterate over only those entries containing "Chicos" to look whether the second entry (say "Blue") is there

Please check and let know whether the speed improves

Sub pivot_table_select()


    Dim field1 As String
    Dim ws As Worksheet
   
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
   
    Dim inp_array() As String
    Dim i, j, pi_count, inp_count As Integer
   
               
    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)
   
    pf.ClearAllFilters
   
    'For Each pi In pf.PivotItems
    '   pi.Visible = True
    'Next pi
               
    inp = InputBox("Please enter", , "Please separate multiple strings with a space") 'Separate multiple strings with a space
   
    If Len(Trim(inp)) = 0 Then
        MsgBox ("No input provided")
        Exit Sub
    End If
   
      
    inp_array = Split(inp, " ")
   
    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.PivotFilters.Add Type:=xlCaptionContains, Value1:="*" & inp_array(0) & "*"
    inp_count = inp_count + 1
               
    pi_count = 1
   
    If UBound(inp_array) > 0 Then
   
        For Each pi In pf.PivotItems
            If pi.Visible Then
                pi_count = pi_count + 1
                For j = 1 To UBound(inp_array)
                    If InStr(1, pi.Name, inp_array(j), vbTextCompare) = 0 Then
                        pi.Visible = False
                        Exit For
                    End If
                Next j
                If pi_count Mod 100 = 0 Then
                    ActiveSheet.Cells(Int(pi_count / 100), 7) = pi_count
                End If
            End If
           
        Next pi
    End If
   
   
End Sub

 

[frewert]

I think it took about 20 seconds or so to get 100.

Maybe a different approach is necessary.

I asked someone on reddit about this and here is their suggestion,

"
A pivot table can only search for one criteria. You want to search for two. One way to search for two is to make a duplicate of your rows field in your original dataset. For example, if your pivot table rows field is named Clothing, then in your original dataset make a helper column named ClothingDup. In your original dataset, you'll have your original Clothing column, and you'll have the helper column which is a duplicate of and linked to Clothing.

Then, refresh your pivot table source to include the new helper column as a field. Drag both Clothing (original) and ClothingDup to rows. Now, you can search Clothing for "cato", then search ClothingDup for "black". Hide one of the columns from the pivot table if you don't want to see it.

Someone adept at vba might have a vba solution for this.
"

 

[frewert]

How about an alternate approach ?

I thought about this idea. Infact, I've implemented a similar idea already. The raw data is only 1 column, Item Title. I use a vba code to pull out 4 columns of data from it, Gender, Size, Condition, and Category. I could in theory create another column that is called Features where I could include include stuff like the various colors, materials, styles etc in a small table.

 

[frewert]

Thanks for all of your help guys. I finally found a good solution to this.

I used a duplicate column of Item Title in the source table.

I then did the same method I linked to in the Youtube video but duplicated that. I now have 2 little arrow buttons that bring up a search box.

 

[Alex Blakenburg]

Thanks for letting us know.

 

[arun_eagertolearn]

Thanks for letting know