Searching for text

[ArthurADs]

I'm trying to create a function of the following thing:

Search on the sheet "2", in a column "B" (where exist a bunch of cells full of text) a cell that contains info from 2 cells from sheet "1" in columns "A" and "B". For example: On sheet "1" a have 1 cell with "Apple" and the second cell with "Pear", and i need to find on sheet "2" column "B", a recipe that contains "Apple" and "Pear" (those words coming from the two cells from sheet 1), even if those two words are separated inside the cell on sheet "B".  Then create a drop down with the results of the search previously made. 

I'm attaching 2 files with this example to make it easier to understand the idea. 

[#2]

@ArthurADs 

Hi

Your request is simple , however you have multiple issues with the setup:

1- These are not two "Sheets", these are 2 separate Files "Workbooks". which is NOT an ideal option. Better have the source data (recipes), Your Conditions(Apple, Pear,...) and the final result used for creating drop lists into one single Excel file.

2- The data where we search would better be split into 2 columns say: Recipe name & Recipe details

Once you have a much better setup, tens or hundreds of recipes, and assuming that you will always search using 2 conditions (not necessary Apple & Pear) then the steps are as follows:

1- Create a search function that searches for the keywords

2- Create an Advanced Filter (Data Tab >> Advanced)

3- Run the filter automatically every time the search term changes

I am attaching a sample file with different data where we want to extract records based on 2 conditions selected from 2 drop lists 'region" & "Sales Rep"

this is exactly what you need. Test by changing the Region or Sales Rep from their drop lists

Note : the source list (like your Recipes) is in columns A:F
The conditions (drop lists) are in Cells H2 & I2
The extracted records are in columns K to P

let me help you with an action Plan:
Try to create a list of Recipes more organized and split the data into columns, Having Headers such as: recipe code, Recipe Name, Ingredients, How to prepare...etc

Create 2 lists with the different ingredients you will look for.
i can then help you create the functionality.

a friendly advice, do not use Excel with the same approach we use a word editor. break the data so that Excel can help you extract records.

Good Luck

Nabil Mourad

[#3]

@ArthurADs 

Hi Arthur,

i have managed to get list with recipes, take a loom at the attachment. Is you enter column A or column B the list will be generated automatically. 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngRow As Long
Dim lngRowMax As Long
Dim strBuf As String

If Target.Column < 3 Then

With Cells(Target.Row, 3).Validation
.Delete
lngRowMax = Sheet2.Range("B" & Sheet2.Rows.Count).End(xlUp).Row
For lngRow = 2 To lngRowMax

If InStr(LCase(Sheet2.Range("B" & lngRow).Value), LCase(Cells(Target.Row, 1).Value)) > 0 And _
InStr(LCase(Sheet2.Range("B" & lngRow).Value), LCase(Cells(Target.Row, 2).Value)) > 0 Then

strBuf = strBuf & Sheet2.Range("B" & lngRow).Value & "-"

End If

Next lngRow

strBuf = Left(strBuf, Len(strBuf) - 1)
strBuf = Replace(strBuf, ";", ".")
strBuf = Replace(strBuf, ".", ".")
strBuf = Replace(strBuf, ",", ".")
strBuf = Replace(strBuf, "-", ",")

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, Operator:=xlBetween, Formula1:=strBuf

End With

End If

End Sub

Best regards 

Bernd

https://vba-tanker.com/

[#4]

@nabilmourad Hey, thanks for your correction related to the "Sheets", haha, I'm pretty new on excel universe, and thanks for you suggestion. Your file is incredible, but it doesn't solve my problem, unfortunetaly. The problem is, there is already a file with all the data related to the recipes, with the following headers: Recipe Name, Author, Descriptions, Ingredients and finally How to do it (the data that exist on "Sheet 2" attached in my post). Since the data "Apple" and "Pear", are surronded by a bunch of text in a single cell inside the column "How to do it", i would need a function that is able to search in those cells (Column "How to do it") those two words "Apple" and "Pear", even if they are not close to each other inside the cell. And then filter, exatly as your "Recipe Example" file does. Does such function exist?

Thanks again

[#5]

@Berndvbatanker Hi Bernd, thanks for your answer. Seems pretty interesting your code, but i know absolutely nothing related to algorithm in general. I dont even know  where should I start using that. Sorry