VBA - AutoFilter an array containing wildcards?

[Tunesmith]

Hi,

First post, longtime lurker.

I'm building a report and as part of that I need to collect certain Invoice IDs based on what type of Product Offers IDs they contain. The crux is that the Invoice IDs can contain multiple Product Offer IDs (separated by semicolons) in no particular order in one cell and I only want to collect the invoices containing the specific products I'm after. From what I can tell an Autofilter array doesn't seem to work with wildcards so I'm looking for help.

I've attached an example .csv containing InvoiceID and OfferList columns. There are 12 invoices with a set of product offers (Item.OFD.50.00001 through Item.OFD.50.00008).

For the sake of the example I'm looking to collect Invoice IDs 1-7 and 10-12 which contain offers 1, 2, 4, 5, 6, 7 anywhere in them but not invoices 8-9 that only contain offers 3 and 8. In my real report there are hundreds of offers of which I'm looking to collect around 50. The offers I want to collect are always known but the actual total offer IDs in the source files are unknown.

This is the code part I've made, which only works for Invoices where there is only one product offer per cell, I'm looking to expand this so it suits my outlined example with multiple products per invoice.

Sub Collect_InvoiceIDs()

    Dim sheetRng As Range
       
    Set sheetRng = ActiveSheet.Range("A1:B" & Range("A" & Rows.Count).End(xlUp).Row)
        sheetRng.AutoFilter Field:=2, Criteria1:=Array( _
        "Item.OFD.50.00001", "Item.OFD.50.00002", "Item.OFD.50.00004", _
        "Item.OFD.50.00005", "Item.OFD.50.00006", "Item.OFD.50.00007"), _
        Operator:=xlFilterValues
        sheetRng.Select
        sheetRng.Copy
        Sheets.Add After:=Sheets(Sheets.Count)
        ActiveSheet.Paste
        ActiveSheet.Name = "Selected Invoices"
        Sheets(1).Delete
        Range("A1").Select

        Set sheetRng = Nothing

End Sub

Many thanks for any help.