Filter, isnumber, search a range of criteria

[shakethingsup]

Hi, I'm good with filter, isnumber, search when there is one criteria to search for.

Now I have a range instead of one piece of text to search for.

My original formula for one criteria was in cell D1 with my search criteria in C1:
=Filter(A:A,isnumber(search(C1,B:B)))

Now I have search criteria in C1 and C2:
- I saw a sumproduct formula but that seems to only search one cell at a time
=SUMPRODUCT(--ISNUMBER(SEARCH(C1:C2,B1)))>0

How do I combine all of this together? Filter + isnumber + search + range of criteria to search? I'm trying to search the forums but am missing something.

Thank you in advance,
Mayank

 

[Peter_SSs]

After reviewing the BYRow function, which retrieves all rows and takes a lambda parameter named 'rw', I understand that the 'rw' parameter is used to represent the items that result from the ByRow operation.

Is my understanding correct?

Yes, but there is nothing special about using "rw" specifically. You could use whatever 'variable' name you want there.

I'm curious to know how you learned these amazing formulas.

Mostly by studying threads in this forum and plenty of trial & error.

 

[Andrew123456789]

Would something like one of these be any use to you? Column E searches for the text strings, column G searches for whole words only.
The column C range of things to look for can be as large as you like.

23 05 09.xlsm
	A	B	C	D	E	F	G
1	Data	Text	cat
2	Data 1	dog	dog		Data 1		Data 1
3	Data 2	scatter	rat		Data 2		Data 5
4	Data 3	pig			Data 5
5	Data 4	horse			Data 6
6	Data 5	grey rat
7	Data 6	rather not
Filter multiple values

Cell Formulas
Range	Formula
E2:E5	E2	=FILTER(A2:A7,BYROW(B2:B7,LAMBDA(rw,COUNT(SEARCH(C1:C3,rw)))))
G2:G3	G2	=FILTER(A2:A7,BYROW(B2:B7,LAMBDA(rw,COUNT(SEARCH(" "&C1:C3&" "," "&rw&" ")))))
Dynamic array formulas.

Hi Peter,
This is amazing. Is there a way to use this but also excude words?

 

[Peter_SSs]

Is there a way to use this ...

Welcome to the MrExcel board!
Which? There were two different solutions suggested in that post you have quoted.

... but also excude words?

Exclude words?

It Would be helpful if you provided a small but representative set of dummy sample data and the expected results with XL2BB and then explain just what you are trying to do with reference to the sample data.

 

[Andrew123456789]

Welcome to the MrExcel board!
Which? There were two different solutions suggested in that post you have quoted.


Exclude words?

It Would be helpful if you provided a small but representative set of dummy sample data and the expected results with XL2BB and then explain just what you are trying to do with reference to the sample data.

Hi,
Sorry I was a bit vague. It's the Lambda function. Unfortunately I can't install XL2BB so I hope the screenshot is ok. I'm trying to sort stock in to groups so in 1 instance I would want to find Exam tables and Exam desks but exclude Premium, Skid and Trolley but in another search for just Exam Trolleys etc.

 

[Peter_SSs]

It's the Lambda function.

Both solutions in the marked post use the LAMBDA function but the results are different depending on what you are trying to do. Please read the text above my mini sheet in the marked solution post & clarify which of those search methods you require.
The formula that you have shown in your image suggests that if column B happened to have an entry "Exam Tablets for sale" then you would want that entry included in your results. Is that correct?

 

[Andrew123456789]

I was using the string search version which gave me my base data searching for Exam Desk and Exam table and I'm looking to remove results to get a refined list. For instance, I would want to remove anything with Skid, Premium or trolley. I'm already getting the results I need using isnumber & iserror searches but the formulas are very long and messy and your way is a lot neater and easier to amend.

 

[Peter_SSs]

I was using the string search version

OK, see if this is it then.

Andrew123456789.xlsm
	A	B	C	D	E	F	G	H
1	A1	Exam Desk		Include these ..	.. but Exclude these		A1	Exam Desk
2	A2	Exam Desk Skid Base		Exam Desk	Skid		A3	Exam Table
3	A3	Exam Table		Exam Table	Premium		A8	Exam Tablets
4	A4	Premium Exam Table			Trolley		A9	Hexam Desk
5	A5	Premium Exam Desk					A10	Non-Exam Table
6	A6	Exam Desk and Trolley
7	A7	Exam Desk Trolley
8	A8	Exam Tablets
9	A9	Hexam Desk
10	A10	Non-Exam Table
11	A11	Ordinary Chair
Sheet1

Cell Formulas
Range	Formula
G1:H5	G1	=FILTER(A1:B11,BYROW(B1:B11,LAMBDA(rw,COUNT(SEARCH($D$2:$D$3,rw))*(COUNT(SEARCH($E$2:$E$4,rw))=0))))
Dynamic array formulas.

 

[Andrew123456789]

OK, see if this is it then.

Andrew123456789.xlsm
	A	B	C	D	E	F	G	H
1	A1	Exam Desk		Include these ..	.. but Exclude these		A1	Exam Desk
2	A2	Exam Desk Skid Base		Exam Desk	Skid		A3	Exam Table
3	A3	Exam Table		Exam Table	Premium		A8	Exam Tablets
4	A4	Premium Exam Table			Trolley		A9	Hexam Desk
5	A5	Premium Exam Desk					A10	Non-Exam Table
6	A6	Exam Desk and Trolley
7	A7	Exam Desk Trolley
8	A8	Exam Tablets
9	A9	Hexam Desk
10	A10	Non-Exam Table
11	A11	Ordinary Chair
Sheet1

Cell Formulas
Range	Formula
G1:H5	G1	=FILTER(A1:B11,BYROW(B1:B11,LAMBDA(rw,COUNT(SEARCH($D$2:$D$3,rw))*(COUNT(SEARCH($E$2:$E$4,rw))=0))))
Dynamic array formulas.

That works perfectly. You are a legend. Thank you so much. This will make amendments so much easier and tidier.

 

[Peter_SSs]

Cheers. Glad to help. Thanks for the follow-up.