Remove duplicate words

[giannip]

Hi everyone,
I am having an issue with removing duplicating words. I have a bunch of data I need combined separated by commas. Example : High Fructose Corn Syrup, corn starch, water, sugar, corn starch, palm oil, salt, yeast, Palm oil, sugarcane, bleached sugar. Desired outcome: High Fructose Corn Syrup, corn starch, water, sugar, palm oil, salt, yeast, sugarcane, bleached sugar.
CONDITIONS: if column I (Supplier) is "Own Brand" then concatenate and remove duplicate values in R(allergens), S(Yes or No), T(ingredients), U(shared facilities) respectively, ONLY if values in column C match. Paste new concatenated string values into R, S, T, U, for Own brand rows in column I.

I do not want a function, I would like a VBA code that I can use in a Macro please. Any help would be deeply appreciated as I am extremely new to this. Baby steps please as I have been using Chatgbt to generate code and it does not provide the exact solution I need.

 

[giannip]

Id Key	allergens	BE item	Ingredient Statement
27	milk, egg, wheat, soy	Yes	Sugar, Water, Bleached Enriched Flour (Wheat Flour, Niacin, Reduced Iron, Thiamine Mononitrate, Riboflavin, Folic Acid), Vegetable Oil (Soybean Oil And/Or Palm Oil And/Or Canola Oil), Cocoa (Processed With Alkali), Dried Whole Eggs, Vegetable Shortening (Palm Oil, Soybean Oil, Propylene Glycol Mono- And Diesters Of Fats And Fatty Acids, Mono- And Diglycerides, Soy Lecithin). Contains 2% Or Less Of Each Of The Following: Soy Lecithin, Nonfat Dry Milk, Modified Food Starch (Corn), Sodium Bicarbonate, Dried Egg Whites, Wheat Starch, Salt, Sodium Aluminum Phosphate, Mono- And Diglycerides, Emulsifier (Propylene Glycol Mono- And Diesters Of Fats And Fatty Acids, Mono- And Diglycerides, Soy Lecithin), Sodium Acid Pyrophosphate, Polysorbate 60, Artificial Flavor, Sodium Stearoyl Lactylate, Guar Gum, Monocalcium Phosphate, Xanthan Gum, Soy Flour.
27	soy	Yes	Sugar, Vegetable Shortening (Palm Oil, Canola Oil, Mono- And Diglycerides, Polysorbate 60), Water, Cocoa (Processed With Alkali). Contains 2% Or Less Of Each Of The Following: High Fructose Corn Syrup, Corn Syrup, Soybean Oil, Corn Starch, Salt, Natural And Artificial Flavor, Phosphoric Acid, Guar Gum, Potassium Sorbate (Preservative), Sodium Benzoate (Preservative).
29	milk, egg, wheat, soy	Yes	Sugar, Water, Bleached Enriched Flour (Wheat Flour, Niacin, Reduced Iron, Thiamine Mononitrate, Riboflavin, Folic Acid), Vegetable Oil (Soybean Oil And/Or Palm Oil And/Or Canola Oil), Cocoa (Processed With Alkali), Dried Whole Eggs, Vegetable Shortening (Palm Oil, Soybean Oil, Propylene Glycol Mono- And Diesters Of Fats And Fatty Acids, Mono- And Diglycerides, Soy Lecithin). Contains 2% Or Less Of Each Of The Following: Nonfat Dry Milk, Modified Food Starch (Corn), Sodium Bicarbonate, Soy Lecithin, Dried Egg Whites, Wheat Starch, Salt, Sodium Aluminum Phosphate, Mono- And Diglycerides, Emulsifier (Propylene Glycol Mono- And Diesters Of Fats And Fatty Acids, Mono- And Diglycerides, Soy Lecithin), Sodium Acid Pyrophosphate, Polysorbate 60, Artificial Flavor, Sodium Stearoyl Lactylate, Guar Gum, Monocalcium Phosphate, Xanthan Gum, Soy Flour. Nutritional Information
29	Milk, Wheat, Soy, Coconut	Yes	High Fructose Corn Syrup, Hydrogenated Vegetable Oil (Palm, Palm Kernel, Coconut And/Or Cottonseed), Water, Oreo® Cookie Pieces (Enriched Unbleached Wheat Flour (Wheat Flour, Niacin, Reduced Iron, Thiamine Mononitrate, Riboflavin, Folic Acid), Sugar, Palm And/Or Canola Oil, Cocoa Alkali Processed, Invert Sugar, Leavening (Baking Soda And/Or Calcium Phosphate), Soy Lecithin, Salt, Chocolate, Natural Flavor), Contains Less Than 2% Of The Following: Sodium Caseinate (A Milk Derivative), Artificial Flavor, Sugar, Polysorbate 60, Carbohydrate Gum, Salt, To Preserve Freshness (Potassium Sorbate), Soy Lecithin, Polyglycerol Esters Of Fatty Acids, Soy Protein Concentrate, Xanthan Gum.

Id Key	allergens	BE item	Ingredient Statement	Cross Contaminate
27	milk, egg, wheat, soy, coconut	yes	Sugar, Water, Bleached Enriched Flour (Wheat Flour, Niacin, Reduced Iron, Thiamine Mononitrate, Riboflavin, Folic Acid), Vegetable Oil (Soybean Oil And/Or Palm Oil And/Or Canola Oil), Cocoa (Processed With Alkali), Dried Whole Eggs, Vegetable Shortening ( Propylene Glycol Mono- And Diesters Of Fats And Fatty Acids, Mono- And Diglycerides, Soy Lecithin). Contains 2% Or Less Of Each Of The Following: Nonfat Dry Milk, Modified Food Starch (Corn), Sodium Bicarbonate, Dried Egg Whites, Wheat Starch, Salt, Sodium Aluminum Phosphate, Mono- And Diglycerides, Emulsifier (Propylene Glycol Mono- And Diesters Of Fats And Fatty Acids, Mono- And Diglycerides, Soy Lecithin), Sodium Acid Pyrophosphate, Polysorbate 60, Artificial Flavor, Sodium Stearoyl Lactylate, Guar Gum, Monocalcium Phosphate, Xanthan Gum, Soy Flour, Vegetable Shortening (Polysorbate 60), High Fructose Corn Syrup, Corn Syrup, Corn Starch, Salt, Natural And Artificial Flavor, Phosphoric Acid, Guar Gum, Potassium Sorbate (Preservative), Sodium Benzoate.	may contain other allergens due to shared facility.
29	milk, egg, wheat, soy	yes	sugar, water, bleached enriched flour (wheat flour, niacin, reduced iron, thiamine mononitrate, riboflavin, folic acid), vegetable oil (soybean oil and/or palm oil and/or canola oil), cocoa (processed with alkali), dried whole eggs, vegetable shortening (ropylene glycol mono- and diesters of fats and fatty acids, mono- and diglycerides, soy lecithin). contains 2% or less of each of the following: nonfat dry milk, modified food starch (corn), sodium bicarbonate, dried egg whites, wheat starch, salt, sodium aluminum phosphate, emulsifier, sodium acid pyrophosphate, polysorbate 60, artificial flavor, sodium stearoyl lactylate, guar gum, monocalcium phosphate, xanthan gum, soy flour, polysorbate 60),). contains 2% or less of each of the following: high fructose corn syrup, corn syrup, corn starch, natural and artificial flavor, phosphoric acid, potassium sorbate (preservative), sodium benzoate .

 

[giannip]

this is the desired outcome *

Id Key	allergens	BE item	Ingredient Statement
27	milk, egg, wheat, soy	yes	sugar, water, bleached enriched flour (wheat flour, niacin, reduced iron, thiamine mononitrate, riboflavin, folic acid), vegetable oil (soybean oil and/or palm oil and/or canola oil), cocoa (processed with alkali), dried whole eggs, vegetable shortening (ropylene glycol mono- and diesters of fats and fatty acids, mono- and diglycerides, soy lecithin). contains 2% or less of each of the following: nonfat dry milk, modified food starch (corn), sodium bicarbonate, dried egg whites, wheat starch, salt, sodium aluminum phosphate, emulsifier, sodium acid pyrophosphate, polysorbate 60, artificial flavor, sodium stearoyl lactylate, guar gum, monocalcium phosphate, xanthan gum, soy flour, polysorbate 60),). contains 2% or less of each of the following: high fructose corn syrup, corn syrup, corn starch, natural and artificial flavor, phosphoric acid, potassium sorbate (preservative), sodium benzoate .
29	milk, egg, wheat, soy, coconut	yes	Sugar, Water, Bleached Enriched Flour (Wheat Flour, Niacin, Reduced Iron, Thiamine Mononitrate, Riboflavin, Folic Acid), Vegetable Oil (Soybean Oil And/Or Palm Oil And/Or Canola Oil), Cocoa (Processed With Alkali), Dried Whole Eggs, Vegetable Shortening (Palm Oil, Soybean Oil, Propylene Glycol Mono- And Diesters Of Fats And Fatty Acids, Mono- And Diglycerides, Soy Lecithin). Contains 2% Or Less Of Each Of The Following: Nonfat Dry Milk, Modified Food Starch (Corn), Sodium Bicarbonate, Soy Lecithin, Dried Egg Whites, Wheat Starch, Salt, Sodium Aluminum Phosphate, Mono- And Diglycerides, Emulsifier (Propylene Glycol Mono- And Diesters Of Fats And Fatty Acids, Mono- And Diglycerides, Soy Lecithin), Sodium Acid Pyrophosphate, Polysorbate 60, Artificial Flavor, Sodium Stearoyl Lactylate, Guar Gum, Monocalcium Phosphate, Xanthan Gum, Soy Flour. High Fructose Corn Syrup, Hydrogenated Vegetable Oil ( Palm Kernel, Coconut And/Or Cottonseed), Water, Oreo® Cookie Pieces (Enriched Unbleached Wheat Flour (, Niacin, Reduced Iron, Thiamine Mononitrate, Riboflavin, Folic Acid), Palm And/Or Canola Oil, Cocoa Alkali Processed, Invert Sugar, Leavening (Baking Soda And/Or Calcium Phosphate), Chocolate, Natural Flavor), Contains Less Than 2% Of The Following: Sodium Caseinate (A Milk Derivative), Artificial Flavor, Sugar, Polysorbate 60, Carbohydrate Gum, Salt, To Preserve Freshness (Potassium Sorbate), Soy Protein Concentrate.

 

[Peter_SSs]

I don't understand the examples. Probably smaller examples would be better and you need to explain the desired results in relation to the sample data that you give.
Also, when using XL2BB it is generally better to use Mini Sheet then Table Only.

 

[giannip]

original data

​
Id Key	allergens	BE item	Ingredient Statement
27	milk, egg, wheat, soy	Yes	Sugar, Water, Bleached Enriched Flour (Wheat Flour, Niacin, Reduced Iron, Thiamine Mononitrate, Riboflavin, Folic Acid), Vegetable Oil (Soybean Oil And/Or Palm Oil And/Or Canola Oil), Cocoa (Processed With Alkali), Dried Whole Eggs, Vegetable Shortening (Palm Oil, Soybean Oil, Propylene Glycol Mono- And Diesters Of Fats And Fatty Acids, Mono- And Diglycerides, Soy Lecithin). Contains 2% Or Less Of Each Of The Following: Soy Lecithin, Nonfat Dry Milk, Modified Food Starch (Corn), Sodium Bicarbonate, Dried Egg Whites, Wheat Starch, Salt, Sodium Aluminum Phosphate, Mono- And Diglycerides, Emulsifier (Propylene Glycol Mono- And Diesters Of Fats And Fatty Acids, Mono- And Diglycerides, Soy Lecithin), Sodium Acid Pyrophosphate, Polysorbate 60, Artificial Flavor, Sodium Stearoyl Lactylate, Guar Gum, Monocalcium Phosphate, Xanthan Gum, Soy Flour.
27	soy, nuts	Yes	Sugar, Vegetable Shortening (Palm Oil, Canola Oil, Mono- And Diglycerides, Polysorbate 60), Water, Cocoa (Processed With Alkali). Contains 2% Or Less Of Each Of The Following: High Fructose Corn Syrup, Corn Syrup, Soybean Oil, Corn Starch, Salt, Natural And Artificial Flavor, Phosphoric Acid, Guar Gum, Potassium Sorbate (Preservative), Sodium Benzoate (Preservative).
29	milk, egg, wheat, soy	Yes	Sugar, Water, Bleached Enriched Flour (Wheat Flour, Niacin, Reduced Iron, Thiamine Mononitrate, Riboflavin, Folic Acid), Vegetable Oil (Soybean Oil And/Or Palm Oil And/Or Canola Oil), Cocoa (Processed With Alkali), Dried Whole Eggs, Vegetable Shortening (Palm Oil, Soybean Oil, Propylene Glycol Mono- And Diesters Of Fats And Fatty Acids, Mono- And Diglycerides, Soy Lecithin). Contains 2% Or Less Of Each Of The Following: Nonfat Dry Milk, Modified Food Starch (Corn), Sodium Bicarbonate, Soy Lecithin, Dried Egg Whites, Wheat Starch, Salt, Sodium Aluminum Phosphate, Mono- And Diglycerides, Emulsifier (Propylene Glycol Mono- And Diesters Of Fats And Fatty Acids, Mono- And Diglycerides, Soy Lecithin), Sodium Acid Pyrophosphate, Polysorbate 60, Artificial Flavor, Sodium Stearoyl Lactylate, Guar Gum, Monocalcium Phosphate, Xanthan Gum, Soy Flour. Nutritional Information
29	Milk, Wheat, Soy, Coconut	Yes	High Fructose Corn Syrup, Hydrogenated Vegetable Oil (Palm, Palm Kernel, Coconut And/Or Cottonseed), Water, Oreo® Cookie Pieces (Enriched Unbleached Wheat Flour (Wheat Flour, Niacin, Reduced Iron, Thiamine Mononitrate, Riboflavin, Folic Acid), Sugar, Palm And/Or Canola Oil, Cocoa Alkali Processed, Invert Sugar, Leavening (Baking Soda And/Or Calcium Phosphate), Soy Lecithin, Salt, Chocolate, Natural Flavor), Contains Less Than 2% Of The Following: Sodium Caseinate (A Milk Derivative), Artificial Flavor, Sugar, Polysorbate 60, Carbohydrate Gum, Salt, To Preserve Freshness (Potassium Sorbate), Soy Lecithin, Polyglycerol Esters Of Fatty Acids, Soy Protein Concentrate, Xanthan Gum.

Desired Outcome

​
Id Key	allergens	BE item	Ingredient Statement
27	milk, egg, wheat, soy, nuts	yes	sugar, water, bleached enriched flour (wheat flour, niacin, reduced iron, thiamine mononitrate, riboflavin, folic acid), vegetable oil (soybean oil and/or palm oil and/or canola oil), cocoa (processed with alkali), dried whole eggs, vegetable shortening (ropylene glycol mono- and diesters of fats and fatty acids, mono- and diglycerides, soy lecithin). contains 2% or less of each of the following: nonfat dry milk, modified food starch (corn), sodium bicarbonate, dried egg whites, wheat starch, salt, sodium aluminum phosphate, emulsifier, sodium acid pyrophosphate, polysorbate 60, artificial flavor, sodium stearoyl lactylate, guar gum, monocalcium phosphate, xanthan gum, soy flour, polysorbate 60),). contains 2% or less of each of the following: high fructose corn syrup, corn syrup, corn starch, natural and artificial flavor, phosphoric acid, potassium sorbate (preservative), sodium benzoate .
29	milk, egg, wheat, soy, coconut	yes	Sugar, Water, Bleached Enriched Flour (Wheat Flour, Niacin, Reduced Iron, Thiamine Mononitrate, Riboflavin, Folic Acid), Vegetable Oil (Soybean Oil And/Or Palm Oil And/Or Canola Oil), Cocoa (Processed With Alkali), Dried Whole Eggs, Vegetable Shortening (Palm Oil, Soybean Oil, Propylene Glycol Mono- And Diesters Of Fats And Fatty Acids, Mono- And Diglycerides, Soy Lecithin). Contains 2% Or Less Of Each Of The Following: Nonfat Dry Milk, Modified Food Starch (Corn), Sodium Bicarbonate, Soy Lecithin, Dried Egg Whites, Wheat Starch, Salt, Sodium Aluminum Phosphate, Mono- And Diglycerides, Emulsifier (Propylene Glycol Mono- And Diesters Of Fats And Fatty Acids, Mono- And Diglycerides, Soy Lecithin), Sodium Acid Pyrophosphate, Polysorbate 60, Artificial Flavor, Sodium Stearoyl Lactylate, Guar Gum, Monocalcium Phosphate, Xanthan Gum, Soy Flour. High Fructose Corn Syrup, Hydrogenated Vegetable Oil ( Palm Kernel, Coconut And/Or Cottonseed), Water, Oreo® Cookie Pieces (Enriched Unbleached Wheat Flour (, Niacin, Reduced Iron, Thiamine Mononitrate, Riboflavin, Folic Acid), Palm And/Or Canola Oil, Cocoa Alkali Processed, Invert Sugar, Leavening (Baking Soda And/Or Calcium Phosphate), Chocolate, Natural Flavor), Contains Less Than 2% Of The Following: Sodium Caseinate (A Milk Derivative), Artificial Flavor, Sugar, Polysorbate 60, Carbohydrate Gum, Salt, To Preserve Freshness (Potassium Sorbate), Soy Protein Concentrate.

The original data exsist on Sheet A, I need the final result on Sheet B. The final result is the concatenated column's IF the ID key matches. All of the Allergens were listed in the desired outcome with no duplicated words. I want to do the same thing to ingredients but be sensitive from removing too much information. EX ID 29 : Keeping "Bleached Enriched Flour" AND "Wheat Flour" and not resulting in "Bleached Enriched Flour" and "Wheat".