Advanced Filtering for Non-Exact Matches

[StevieTabs]

Hello Power Pivoters

I have a general ledger 500k lines long containing a mix of mostly payroll/some AP data . I am trying to pare this list down to only show rows that contain (but are not exact matches of) a list of some 500 names in a certain column. Since there's confidential payroll data, I'm unable to show the actual data, but let me do my best to describe in further detail:

• Main sheet contains a "Description Column" of data that contains the payroll data, which is what I'm trying to filter. I can filter this list easily to only show lines with payroll information (by filtering the vendor name Column to only show me the payroll company's vendor name, and erase everything else).
• On a separate sheet, I have created a list of all the names I am interested in seeing payroll data for on the main sheet.
• I need to be able to see all of this filtered data on the main sheet.
• The typical string of text in the payroll data is <week-ending date> : <payroll employee name> : ,type of payroll data. (where type of payroll data is 1x, OT, SUI, FICA etc.)

I've been trying the Advanced Filter function. I tried using *'s on each side of the names, and also =*name*. None of these are working. Am I using the right function? I'm not sure VLOOKUP will work since I need to see all matches for multiple names.

Any help would be greatly appreciated. This is on MS Excel for Mac, Office Home and Business 2021.

 

[Anonymous1378]

Try the following?

=FILTER(PayrollColumns,(VendorColumn="PayrollVendor")*COUNTIF(DescriptionColumn,"*"&ListOfNames&"*"))