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

sammy1981

New Member
Joined
Jun 28, 2018
Messages
20
[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]
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
@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.
 
Upvote 0
This will deal with the ; & the %. But still only gives one result.
Code:
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
 
Upvote 0
This will deal with the ; & the %. But still only gives one result.
Code:
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?
 
Upvote 0
Given the layout of the data in col A, not that I know of.
 
Upvote 0
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
 
Upvote 0
You can leave the commas between words to check for. and format it so that
Code:
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
Code:
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
Code:
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.
 
Last edited:
Upvote 0
You can leave the commas between words to check for. and format it so that
Code:
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
Code:
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
Code:
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
 
Upvote 0
You can leave the commas between words to check for. and format it so that
Code:
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
Code:
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
Code:
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?
 
Upvote 0

Forum statistics

Threads
1,224,827
Messages
6,181,200
Members
453,022
Latest member
RobertV1609

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top