Using the filter function to return all instances in which criteria matches two other cells, one of which has multiple lines of data

AJPatel73

New Member
Joined
Mar 5, 2025
Messages
1
Office Version
  1. 365
Platform
  1. Windows
0
I have seen many posts for using the filter function for multiple criteria and the solutions works great if both cells that you are looking for a match in only has 1 line of data. I have a spreadsheet where one of the columns in which you are looking for a match has cells that have multiple lines of data separated by carriage return within the cell. I need to be able to see if what I am looking for is within that cell so that the data from another column is returned.

table.JPG


As you can see on the attached table, some cells in column B have multiple lines of data. I created a summary tab that looks like the following attached image with the expected results:

results.JPG


Column A2 in the Results attached is a drop down list where you can select the type of supply

I have a countif statement In B2 that includes a wild card so I get the total # of companies that are active that supply Nuts (this is a cell reference to A2) which in this case is 2

Column C I want to list out the active companies that supply nuts, and that is where I run into issue when I have a company that supplies multiple products (listed in the same cell) in column B of the data table

Amy help you can provide or direct me to where I can get assistance is greatly appreciated
 
Welcome to the MrExcel board!

For the future, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Is this what you want?

25 03 06.xlsm
ABC
1StatusSupplyCompany
2ActiveNuts BoltsABC
3ActiveNutsDEF
4InactiveWashersGHI
5ActiveScrews WashersJKL
6InactiveNutsMNO
7
8
9Supply# of CompaniesCompanies
10Nuts2ABC
11DEF
12
Supply
Cell Formulas
RangeFormula
B10B10=COUNTIFS(B2:B6,"*"&A10&"*",A2:A6,"Active")
C10:C11C10=FILTER(C2:C6,(A2:A6="Active")*ISNUMBER(SEARCH(A10,B2:B6)),"")
Dynamic array formulas.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top