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]
[TABLE="width: 500"]
<tbody>[TR]
[TD]butylhydroxyanisole, lavender oil, methylparaben, purified landolin, purfied water[/TD]
[TD] [/TD]
[TD]A-alpha-C (2-Amino-9H-pyrido[2,3-b]indole)[/TD]
[/TR]
[TR]
[TD]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[/TD]
[TD][/TD]
[TD]Abiraterone acetate[/TD]
[/TR]
[TR]
[TD]CarboxyMethylcellulose sodium, Microcrystalline Cellulose,flavor, Acetylaminofluorene, Purified Water, Red 22, Red 28, Salicylic Acid, [/TD]
[TD][/TD]
[TD]Acetaldehyde[/TD]
[/TR]
[TR]
[TD]FD&C red #40 , propylene glycol, flavoring, sucrose, and water, soybean, oil and corn starch used as processing aids.[/TD]
[TD][/TD]
[TD]Acetamide[/TD]
[/TR]
[TR]
[TD]Famotidine, USP 20mg...... Acid Reducer[/TD]
[TD][/TD]
[TD]Acetazolamide[/TD]
[/TR]
[TR]
[TD]Purified Water, Citric Acid, Sodium Benzoate Octoxynol-9.[/TD]
[TD][/TD]
[TD]Acetochlor[/TD]
[/TR]
[TR]
[TD]Adipic Acid, FD&C Blue 1, FD&C Red 27 , FD&C Yellow 6, Acetaldehyde FD&C Yellow 10[/TD]
[TD][/TD]
[TD]Acetohydroxamic acid[/TD]
[/TR]
[TR]
[TD]Polyehtylene, Sodium Sarcoisnate, EDTA, Quaternium-15, Carbomer, acetate[/TD]
[TD][/TD]
[TD]2-Acetylaminofluorene[/TD]
[/TR]
</tbody>[/TABLE]
[FONT=&quot]
[/FONT]

 

[sammy1981]

Much appreciated.

is Fluffs macro in conjunction with yours or its a separate one?

 

[mumps]

@sammy1981: It is a separate macro. @Fluff: The OP posted this sample of data in column A:

Homosalate 10.0%; Octisalate 5.0%; Oxybenzone 5.0%; Avobenzone 2.0%; Octocrylene 2%, SD Alcohol 40; Acrylates/Octylacrylamide Copolymer; Glycerin; Fragrance

I don't think that column D contains the percentages. Would this cause a problem in isolating the ingredient? Also in this example he uses a ";" as a separator.

 

[Fluff]

This will deal with the ; & the %. But still only gives one result.

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
   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(Replace(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

 

[sammy1981]

This will deal with the ; & the %. But still only gives one result.

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
   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(Replace(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

thanks
this seems to work but if the ingredients in column D is more than one word it wont find it, is there a work around for that?

 

[Fluff]

Given the layout of the data in col A, not that I know of.

 

[sammy1981]

Given the layout of the data in col A, not that I know of.

i can format column A in a way that its uniform without any commas or anything else, just spaces between each word, will use this file as a template and paste the list of ingredients in column as i get them but can format it a certain way before i run the macro

 

[Fluff]

You can leave the commas between words to check for. and format it so that

Active: Eucalyptol 0.092%, Menthol 0.042%, Methyl Salicylate 0.060%, Thymol 0.064%... Purposes: Antiplaque/ Antigingivitis. Inactive: Water, Alcohol (21.6%), Sorbitol Solution, Flavoring, Poloxamer 407, Benzoic Acid, Sodium Saccharin, Sodium Benzoate, D&C Yellow No. 10, FD&C Green No. 3.

becomes something like

Active, Eucalyptol, Menthol, Methyl Salicylate, Thymol, Purposes, Antiplaque/ Antigingivitis, Inactive, Water, Alcohol, Sorbitol Solution, Flavoring, Poloxamer 407, Benzoic Acid, Sodium Saccharin, Sodium Benzoate, D&C Yellow No. 10, FD&C Green No. 3.

then this should work

Sub ListIngredients()
   Dim Cl As Range
   Dim Dic As Object
   Dim sp As Variant
   Dim i As Long
   
   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

Basically anything between the commas must be like the values you want to search for.

 

[sammy1981]

You can leave the commas between words to check for. and format it so that

Active: Eucalyptol 0.092%, Menthol 0.042%, Methyl Salicylate 0.060%, Thymol 0.064%... Purposes: Antiplaque/ Antigingivitis. Inactive: Water, Alcohol (21.6%), Sorbitol Solution, Flavoring, Poloxamer 407, Benzoic Acid, Sodium Saccharin, Sodium Benzoate, D&C Yellow No. 10, FD&C Green No. 3.

becomes something like

Active, Eucalyptol, Menthol, Methyl Salicylate, Thymol, Purposes, Antiplaque/ Antigingivitis, Inactive, Water, Alcohol, Sorbitol Solution, Flavoring, Poloxamer 407, Benzoic Acid, Sodium Saccharin, Sodium Benzoate, D&C Yellow No. 10, FD&C Green No. 3.

then this should work

Sub ListIngredients()
   Dim Cl As Range
   Dim Dic As Object
   Dim sp As Variant
   Dim i As Long
   
   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

Basically anything between the commas must be like the values you want to search for.

perfect, thanks
will let know if i come across any obstacles down the line

 

[sammy1981]

You can leave the commas between words to check for. and format it so that

Active: Eucalyptol 0.092%, Menthol 0.042%, Methyl Salicylate 0.060%, Thymol 0.064%... Purposes: Antiplaque/ Antigingivitis. Inactive: Water, Alcohol (21.6%), Sorbitol Solution, Flavoring, Poloxamer 407, Benzoic Acid, Sodium Saccharin, Sodium Benzoate, D&C Yellow No. 10, FD&C Green No. 3.

becomes something like

Active, Eucalyptol, Menthol, Methyl Salicylate, Thymol, Purposes, Antiplaque/ Antigingivitis, Inactive, Water, Alcohol, Sorbitol Solution, Flavoring, Poloxamer 407, Benzoic Acid, Sodium Saccharin, Sodium Benzoate, D&C Yellow No. 10, FD&C Green No. 3.

then this should work

Sub ListIngredients()
   Dim Cl As Range
   Dim Dic As Object
   Dim sp As Variant
   Dim i As Long
   
   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

Basically anything between the commas must be like the values you want to search for.

whats the exact format column A needs to be? i see plenty of ingredients that have "titanium dioxide" and its not picking it up
also, does it have to match the complete cell in column D?

 

[Fluff]

does it have to match the complete cell in column D?

Yes it does.