search within a column for specific words and display the found words in the adjacent cells

[sammy1981]

[FONT=Helvetica Neue, Helvetica, Arial, sans-serif]see attached file, im trying to find out if any of the ingredients from column D exists in column A and display whatever ingredient is found in column B, is this possible? need to go through lots of ingredients and find anything that needs special instructions [/FONT]
[FONT=Helvetica Neue, Helvetica, Arial, sans-serif]see below example[/FONT]

butylhydroxyanisole, lavender oil, methylparaben, purified landolin, purfied water		A-alpha-C (2-Amino-9H-pyrido[2,3-b]indole)
Corn Starch, d&c Red #27 Aluminum Lake, d & c Red #30 Aluminum Lake, Flavors, Saccharin Sodium A-alpha-C (2-Amino-9H-pyrido[2,3-b]indole		Abiraterone acetate
CarboxyMethylcellulose sodium, Microcrystalline Cellulose,flavor, Acetylaminofluorene, Purified Water, Red 22, Red 28, Salicylic Acid,		Acetaldehyde
FD&C red #40 , propylene glycol, flavoring, sucrose, and water, soybean, oil and corn starch used as processing aids.		Acetamide
Famotidine, USP 20mg...... Acid Reducer		Acetazolamide
Purified Water, Citric Acid, Sodium Benzoate Octoxynol-9.		Acetochlor
Adipic Acid, FD&C Blue 1, FD&C Red 27 , FD&C Yellow 6, Acetaldehyde FD&C Yellow 10		Acetohydroxamic acid
Polyehtylene, Sodium Sarcoisnate, EDTA, Quaternium-15, Carbomer, acetate		2-Acetylaminofluorene

<tbody>
</tbody>

[FONT=&quot]
[/FONT]

 

[mumps]

Welcome to the Forum. You cannot attach a file in this forum. If you want to give access to your file, you would have to upload it to site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[sammy1981]

Welcome to the Forum. You cannot attach a file in this forum. If you want to give access to your file, you would have to upload it to site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

thanks

link is below, i have a list of ingredients in column D and im trying to see if any of them are in the list of ingredients in column A, in column A are the ingredients of certain cosmetics, creams etc and column D are single ingredients that i need to check for

https://www.dropbox.com/s/0rieolo16g2swrf/Prop 65 formula.xlsx?dl=0

 

[mumps]

After looking at your data in column A, there is a problem with returning the found ingredient to column B. Sometimes the ingredient in column D can be found as part of another word in column A, for example, the ingredient "benzene" is found in the string "1,2,4-Trihydroxybenzene". The macro will return the word "benzene" in column B to the right of the cell in column A that contains "1,2,4-Trihydroxybenzene". I'm not sure that is what you want. There is no way that I can see to make the macro ignore the string "1,2,4-Trihydroxybenzene" because of the way that your data in column A is entered. If it would be acceptable to accept every occurrence of the word "benzene", including where it is part of another string, then the following macro will work.

Sub FindIngredient()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim sAddr As String
    Dim rngUniques As Range, ingredient As Range, foundIngredient As Range
    Sheets("Sheet1").Range("D1:D" & LastRow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("D1:D" & LastRow), Unique:=True
    Set rngUniques = Sheets("Sheet1").Range("D2:D" & LastRow).SpecialCells(xlCellTypeVisible)
    If Sheets("Sheet1").FilterMode Then Sheets("Sheet1").ShowAllData
    For Each ingredient In rngUniques
        Set foundIngredient = Range("A:A").Find(ingredient, LookIn:=xlValues, lookat:=xlPart)
        If Not foundIngredient Is Nothing Then
            sAddr = foundIngredient.Address
            Do
                foundIngredient.Offset(0, 1) = ingredient
                Set foundIngredient = Range("A:A").FindNext(foundIngredient)
            Loop While foundIngredient.Address <> sAddr
            sAddr = ""
        End If
    Next ingredient
    Application.ScreenUpdating = True
End Sub

 

[sammy1981]

thanks
it helps to narrow down from a big list and manfully check whatever came up as a match but is there a way to only match up complete words that match?

 

[mumps]

There could be a way, however, based on how the data in column A is entered, I can't see how. Sometimes words are on their own, sometimes they are part of another word, sometimes the words are separated by a comma and sometimes by a space. There doesn't seem to be any pattern or consistency and because macros depend on patterns and consistency, I can't see how to solve this problem. There are members of the Forum with much more expertise than I, so perhaps you could start another thread explaining exactly what the problem is and see if anyone might have a solution. Include a link to your file and if you think it might be helpful, include a link to this thread. If you do find a solution, I would appreciate it if you could send me a private message with a link to that solution. I would look forward to see what that solution looks like.

 

[sammy1981]

if i was to make the list in column A consistent, take out everything besides for space between words, is it doable?

 

[Fluff]

Firstly, please do not start another thread for this.
Secondly, with the data in your file. What results would you expect to see?

 

[mumps]

I could give it a try. The data would have to look something like this:

Ingredients: Alcohol Denat., Acrylates / Octylacrylamide Copolymer, Glycerin, Fragrance.

Ingredients would have to be separated by a comma and a space and if an ingredient is more that one word, each word would have to be separated by a space as in the example above.

 

[Fluff]

This

Sub ListIngredients()
   Dim Cl As Range
   Dim Dic As Object
   Dim sp As Variant
   Dim i As Long
   
   Range("A:A").Replace "/", ",", xlPart, , , , False, False
   Set Dic = CreateObject("scripting.dictionary")
   For Each Cl In Range("D2", Range("D" & Rows.Count).End(xlUp))
      Dic(Cl.Value) = Empty
   Next Cl
   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      sp = Split(Cl.Value, ",")
      For i = 0 To UBound(sp)
         If Dic.exists(Trim(sp(i))) Then Cl.Offset(, 1) = Trim(sp(i)) & ", " & Cl.Offset(, 1).Value
      Next i
   Next Cl
End Sub

gives only one result. Is that correct?