EXCEL, MATH, SCIENCE problem..... anyone have any thoughts?

[-GoBengals-]

ill move this after i post it but wanted everyone to see the moved like or max exposure.

 

had some KILLER help with a excel formula sheet to help converting some botches recipes last year.. so figured id come back to the well.

 

SO.

 

I have a flavoring. i have about half a gallon left, the supplier is on the brink of going out of business. I use this product in about 50,000 bottles a year that i sell. it is fairly vital.

 

I have asked if they will sell me the recipe, no answer after a week.

 

I can stock up to an extent but its $400 a gallon, and i use 2-5 gallons a month, so even if i spent $10,000 on it, it would last me 6-9 months, max.

 

So. if i send off a sample to a lab i can get a report of every chemical in it and how much.

 

also, i KNOW that this product is made by this supplier buying ANOTHER companies product, and adding some other things to it.

 

every OTHER company has SDS sheets that tell me what chemicals are in theirs. If i were clever enough I could make a a excel sheet or database of some sort, enter in the details of the test results, and those of 10-20 other flavors that are similar.

 

the pat i dont know how to accomplish is how to forumlate the combination or flavors to equal the main one.

 

example:

 

Vanilla #1 i need to replicate has:

 

ingredient 1 .05%

ingredient 2 5%

ingredient 3 11%

ingredient 4 .9%

and so on to like 50 ingredients/chemicals

 

so id love to cross reference if Vanilla #2 has an ingredient thats NOT in vanilla #1, i can cross it off, if Vanilla #3 has SOME of whats in #1 but lower levels, perfect, its likely in the recipe, then if Vanilla #4 also has some, is there a mixture of #3 and #4 that make up #1?

 

then the wild card that fucks me, if they have like a caramel or a butterscotch added in, it opens up like a cross refernce of 100 flavors vs the 10-20 vanillas im targeting first. but if i can find the BASE ingredient in it, as im positive 80-90% of it is a one flavor base thats laready on the market. thats a great head start.

 

anyone have any idea how i can cross refernce the ingredient lists with eachother and even potentially work out what percentage of multiple products would match the ingredient list of my test results?

 

Note: Vanilla #1 is a homemmade DIY flavoring house so they didnt register with the FDA or create SDS data sheets like everyone else in the flavoring universe did. otherwise id save myself $500 on a mass spec test breakdown.

 

any thoughts or tips are welcome!

 

 

 

 

[Le Tigre]

Haven’t a clue as to the primary questions, however…

 

Have you—or your business attorney—verified copyrights and/or recipe ownership rights? I know they haven’t responded as to sale…but there may be other people involved other than the primary “owner”. 
 

Big/bad potential bad stuff against you and your business if use without permission or sale of product rights by ALL involved parties occurs. 

[PatternMaster]

Have you tried asking ChatGPT..

[Cricket]

7 hours ago, GoBengals said:

Note: Vanilla #1 is a homemmade DIY flavoring house so they didnt register with the FDA or create SDS data sheets like everyone else in the flavoring universe did.

1) Is it legal for them to NOT be registered with the FDA and NOT create SDS data sheets?  I have a hard time believing that being homemade exempts you from registering your product.  If people are ingesting eye-of-newt, they need to know that they are ingesting eye-of-newt.  What if I have an eye-of-newt allergy?
 

2) Be sure to heed Le Tigre’s legal warnings.  I wonder how much you would need to vary the ratios to avoid being “the same” product.  

 

3) I found this for comparing two lists in Excel:

https://www.indeed.com/career-advice/career-development/compare-two-lists-in-excel

 

Just thinking through this, I would warn you to not be too quick to exclude a product listing a “unique” ingredient.  Any little typo in either list, or an ingredient with more than one spelling, or an ingredient known by different names in different languages will show up as “unique” when it is not. 
 

4) Once you (carefully) eliminate the potential products with “unique” ingredients, and start figuring out the combination of the remaining potential products, get back to me.  I love a good challenge, and enjoyed coming up with a solution (no pun intended) to your last quandary.

 

5) Best case scenario is that they get back to you and sell you the recipe.  Keep pursuing this Avenue.  
 

6) Good luck!

 

🦗

[PatternMaster]

Have you tried asking ChatGPT or Wolfram Alpha

[LostInDaJungle]

X axis - Ingredients
Y axis - Flavors

=SUMPRODUCT((Ingredient1_Column=Ingredient1_Reference)*(Ingredient2_Column=Ingredient2_Reference)*(Ingredient3_Column=Ingredient3_Reference), (Percentage1_Column*Percentage1_Reference+Percentage2_Column*Percentage2_Reference+Percentage3_Column*Percentage3_Reference))

 

Ingredient1_Column=Ingredient1_Reference: This part compares the ingredient name in the "Ingredient1" column of the current row to the ingredient name in the "Ingredient1" reference cell. It returns TRUE if they match, FALSE otherwise.

Percentage1_Column*Percentage1_Reference+Percentage2_Column*Percentage2_Reference+Percentage3_Column*Percentage3_Reference: This part calculates the weighted sum of the percentages. It multiplies the percentage of each ingredient in the current row by the corresponding percentage in the reference row and adds them together.

SUMPRODUCT(...): This function multiplies the results of the ingredient matching (0 or 1) by the weighted percentage sum. So, if the ingredients match, it returns the weighted percentage sum; otherwise, it returns 0.

The end result would be a column that says Vanilla #1 has 84% similarity of ingredients to Vanilla #2.

[-GoBengals-]

On 2/25/2025 at 4:29 AM, Le Tigre said:

Haven’t a clue as to the primary questions, however…

 

Have you—or your business attorney—verified copyrights and/or recipe ownership rights? I know they haven’t responded as to sale…but there may be other people involved other than the primary “owner”. 
 

Big/bad potential bad stuff against you and your business if use without permission or sale of product rights by ALL involved parties occurs. 

 

Yea there is no issue in that regard. to put it in my stupid terms "its not that serious".

 

example. This is a guy who had a retail store and in that store he solid DIY flavorings, like if you had a soda shop and could sell lemon lime coke by adding lemon and lime to coca cola, so he took his top "recipes" and slapped a label on them, but i know for 100% fact its the equivalent of buying coke and pepsi and mixing them together. both are on the market and able to be purchased by anyone. I just dont know what version of coke and pepsi they are using to make "super soda". So i am currently taking the long, expensive, slow, route, to making the end product because im paying some asshole to mix two publicly sold ingredients together before up-charging and selling them to me, AND im about to lose the ability to even pay that upcharge and waste of time to make my product.

 

if that makes sense.

 

On 2/25/2025 at 5:34 AM, PatternMaster said:

Have you tried asking ChatGPT..

 

i did not think i could articulate the problem well enough for it to understand.

 

On 2/25/2025 at 8:23 AM, Cricket said:

1) Is it legal for them to NOT be registered with the FDA and NOT create SDS data sheets?  I have a hard time believing that being homemade exempts you from registering your product.  If people are ingesting eye-of-newt, they need to know that they are ingesting eye-of-newt.  What if I have an eye-of-newt allergy?
 

2) Be sure to heed Le Tigre’s legal warnings.  I wonder how much you would need to vary the ratios to avoid being “the same” product.  

 

3) I found this for comparing two lists in Excel:

https://www.indeed.com/career-advice/career-development/compare-two-lists-in-excel

 

Just thinking through this, I would warn you to not be too quick to exclude a product listing a “unique” ingredient.  Any little typo in either list, or an ingredient with more than one spelling, or an ingredient known by different names in different languages will show up as “unique” when it is not. 
 

4) Once you (carefully) eliminate the potential products with “unique” ingredients, and start figuring out the combination of the remaining potential products, get back to me.  I love a good challenge, and enjoyed coming up with a solution (no pun intended) to your last quandary.

 

5) Best case scenario is that they get back to you and sell you the recipe.  Keep pursuing this Avenue.  
 

6) Good luck!

 

🦗

 

1. technically no, but its basically just a dude doing it on the down low, on the side, i get charges via some t shirt business his wife owns, hes been "out of business" for years from when the FDA deadline hit, and i know the products in the mix are registered with the FDA, so i know its all fine, there is just a blinder in there.

 

2. shouldnt be any problems there, details in my response to him. but a great warning none the less.

 

3. this may actually come in very handy as i need to convert my retail database lists from one rewards program to another, and rewards program 1 export customer email and rewards amount, and list 2 needs customer ID, so i can export all customers with customer id, but then i need to match up, via email address or phone number, to move the rewrds total balances from one list to the other. This just may do that perfectly. Thanks! If i can get it to compare 5-10 lists at once, it may work for the recipe as well.

 

4. 100% will do! Thanks!

 

5. you would think, we had a deal with a product maker years ago, we traded products, we made a product they made one and we just traded, neither giving the other the recipe, but it was "as if" it was both of ours... they decided to close down and took that recipe to their grave, never sold it ever again, went into apartment leasing, i offered them $20,000 for the recipe, they ignored me. So i did some sherlock work, went off their flavor descriptions, found key word crossover in flavoring concentrate descriptions and found the ingredients (were only 4) and did about 300 trial runs of various percentages of each, got it really close added another ingredient we felt improved it... and rolled it out ourselves.

 

6. Thanks! Having little time and little money to throw at the project is making it harder.

 

On 2/25/2025 at 11:13 AM, LostInDaJungle said:

X axis - Ingredients
Y axis - Flavors

=SUMPRODUCT((Ingredient1_Column=Ingredient1_Reference)*(Ingredient2_Column=Ingredient2_Reference)*(Ingredient3_Column=Ingredient3_Reference), (Percentage1_Column*Percentage1_Reference+Percentage2_Column*Percentage2_Reference+Percentage3_Column*Percentage3_Reference))

 

Ingredient1_Column=Ingredient1_Reference: This part compares the ingredient name in the "Ingredient1" column of the current row to the ingredient name in the "Ingredient1" reference cell. It returns TRUE if they match, FALSE otherwise.

Percentage1_Column*Percentage1_Reference+Percentage2_Column*Percentage2_Reference+Percentage3_Column*Percentage3_Reference: This part calculates the weighted sum of the percentages. It multiplies the percentage of each ingredient in the current row by the corresponding percentage in the reference row and adds them together.

SUMPRODUCT(...): This function multiplies the results of the ingredient matching (0 or 1) by the weighted percentage sum. So, if the ingredients match, it returns the weighted percentage sum; otherwise, it returns 0.

The end result would be a column that says Vanilla #1 has 84% similarity of ingredients to Vanilla #2.

this is magnificent, ill try to start collecting data (worst part is most companies give PDFs so unless a good pdf to excel converter works on it I have ti enter it in manually).

 

Thanks!

[Hooky]

Can you copy and paste? I guess you may have to manipulate the strings if it just pastes into the first column. Maybe paste it in a csv and use delimiter.

[Cricket]

You convert a PDF to Word or Excel if you have Acrobat Pro.   [Looks like you can get a 7-day free trial.]

 

Use ”Export a PDF” option. 
 

I have no idea how good of a job Acrobat Pro might do, never used it.
 

I DO know that I have successfully converted Word (text) into Excel at work, many years ago…so if it doesn’t transfer into Excel very well, get it into Word.  From there it should fairly easily convert into Excel.

[|westside bengal|]

My former work experience with SDS sheets is that not every chemical is always in there.  But these were industrial formulations and not food products but I have seen instances where things are hidden not to give away trade secrets.  In the food industry the term “natural flavorings” or “spices” is similar.

 

Also use the CAS# if possible and not the chemical name from SDS sheets.  The CAS# is the true identifier.  For example look up Acetone.  It has a several names in the chemistry world but they all have the identical CAS#. 

[-GoBengals-]

30 minutes ago, westside bengal said:

My former work experience with SDS sheets is that not every chemical is always in there.  But these were industrial formulations and not food products but I have seen instances where things are hidden not to give away trade secrets.  In the food industry the term “natural flavorings” or “spices” is similar.

 

Also use the CAS# if possible and not the chemical name from SDS sheets.  The CAS# is the true identifier.  For example look up Acetone.  It has a several names in the chemistry world but they all have the identical CAS#. 

 

yes! I didnt know if anyone knew or would care about the details of that part, so I didnt dive in too far. I figure if i can manage finding the process, regardless of the data source, i can hopefully accomplish the goal... Im sure ill be back with a data dump and see what folks think i may be missing or may be omitted. 

 

Currently down 3-5 people at my warehouse depending on the day so sadly im doing 2-3 peoples jobs in the meantime, then i can dive back into this project.

 

Did some brief comparisons with similar flavorings,  there is something there, there are a few that are so damn similar. whats odd is one, has a bourbon burn to it, so if its involved they had to use something to eliminate that burning flavor, which is probably too high tech for these folks...