.autofilter to pick certain words/phrases and ignore others

Question

Its my intention to filter a load of IT Equipment in a list of about 10,000 to only show tablets, but only those that don't have sims. I would need to filter the word "Ipad" or "Samsung Tablet", but exclude "4G", "5G" and "CELL". Since I need to look for the phrase within a string I've got asterixis before and after.

Sample data would look something like this:

1. "IPAD 10.2"" 64GB WIFI+CELL SP GRY"
2. "IPAD 10.2"" 64GB WIFI+CELL SP GRY"
3. "APPLE IPAD",
4. "Apple iPhone 11 64GB (CO)"
5. "MICROSOFT SURFACE PRO 7+"

So the only data that should show is #3. 1&2 contain strings that aren't needed and 4&5 aren't Ipads or Samsung's.

I have tried the following:

Dim ws As Worksheet
Dim rng As Range
Dim lastRow As Long

Set ws = ActiveWorkbook.Sheets("Input")
lastRow = ws.Range("H" & ws.Rows.Count).End(xlUp).Row
Set rng = ws.Range("H1:H" & lastRow)

With rng

    'I thought this would work
    .AutoFilter Field:=1, Criteria1:=Array("*IPAD*", "*SAMSUNG TABLET*", "<>*CELL*", "<>* 4G*", "<>* 5G*")

    'Also tried
    '.AutoFilter Field:=1, Criteria1:=Array("*IPAD*", "*SAMSUNG TABLET*") ', Criteria2:=Array("<>*CELL*", "<>* 4G*", "<>* 5G*")

The first one appears to work by filtering out the sim'd devices, but it includes everything else like the mobiles and laptops. It doesn't ONLY show tablets.

I don't have much hope for the second one, I know something is wrong but I've been looking for help on other websites and been tinkering for a while and can't just show the intended list.

Edit: So adding this seems to filter all Ipads and Samsung devices,

.AutoFilter Field:=1, Criteria1:=Array("*IPAD*", "*SAMSUNG TABLET*"), Operator:=xlFilterValues

But adding <>5G or <>Cell breaks it, It decides to show everything. I can search by just "Cell" but adding a <> within the array just ruins it. Any Ideas?

Answer 1

Maybe something like this?

Sub test()
Dim arr(): Dim arrResult()
Dim rg As Range: Dim i As Long

Set rg = Range("H2:H12")
arr = Application.Transpose(rg)

For i = LBound(arr) To UBound(arr)
    If InStr(arr(i), "Samsung Tablet") <> 0 Or InStr(arr(i), "IPAD") <> 0 Then
        If InStr(arr(i), "64GB") <> 0 Then arr(i) = Replace(arr(i), "64GB", "!@!")
            If InStr(arr(i), "CELL") = 0 And InStr(arr(i), "4G") = 0 And InStr(arr(i), "5G") = 0 Then
                If InStr(arr(i), "!@!") <> 0 Then arr(i) = Replace(arr(i), "!@!", "64GB")
                j = j + 1
                ReDim Preserve arrResult(1 To j)
                arrResult(j) = arr(i)
            End If
    End If
Next i

With rg
.AutoFilter Field:=1, Criteria1:=arrResult, Operator:=xlFilterValues
End With

End Sub

The sub result will show the yellow row :

The sub put the data value into array, and check each item in array, if the item has the wanted value then check, if the item contains "64GB" it replace for temporary the "64GB" into "!@!". Then it check again, if item does not contain "CELL", "4G" and "5G" then check if it has "!@!" then put back the "64GB" to the item and put the item value into another array, arrResult variable.

Finally it filter the data with arrResult.