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]

 

[sammy1981]

got it, thanks

 

[sammy1981]

in the file i attached i dont know necessarily that any ingredients in column D would be found in column A but i need to go through lists and lists of ingredients and check if any do have them
for example i would want to find the ingredient Oxybenzone in this sunscreen and all the ingredients are in the same cell "Homosalate 10.0%; Octisalate 5.0%; Oxybenzone 5.0%; Avobenzone 2.0%; Octocrylene 2%, SD Alcohol 40; Acrylates/Octylacrylamide Copolymer; Glycerin; Fragrance"

 

[Fluff]

If you do not know the expected outcome, it makes it very difficult to write a macro that works.

 

[sammy1981]

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?

whats this macro suppose to do?

 

[sammy1981]

If you do not know the expected outcome, it makes it very difficult to write a macro that works.

i want that whatever ingredient is found to display it in column B and whatever is not found leave the cell in column B empty or just put NA

 

[mumps]

@Fluff: Terrific solution!
Would you mind explaining:

Dic(Cl.Value) = Empty

Can I assume that this line:

If Dic.exists(Trim(sp(i))) Then Cl.Offset(, 1) = Trim(sp(i)) & ", " & Cl.Offset(, 1).Value

takes into account the possibility that a cell in column A may contain 2 or more different ingredients?

 

[sammy1981]

can be also a formula if possible

 

[Fluff]

whats this macro suppose to do?

Find the Ingredients. But you will have to check to see if it works.

 

[Fluff]

@Fluff: Terrific solution!
Would you mind explaining:

Dic(Cl.Value) = Empty

It adds the value of CL to the dictionary (if it doesn't already exist) & makes the item "empty".
It basically does the same as

If Not Dic.exists(Cl.Value) Then Dic.Add Cl.Value, Nothing

Can I assume that this line:

If Dic.exists(Trim(sp(i))) Then Cl.Offset(, 1) = Trim(sp(i)) & ", " & Cl.Offset(, 1).Value

takes into account the possibility that a cell in column A may contain 2 or more different ingredients?

Thats right

 

[mumps]

Much appreciated.