Need some creative ideas

[automationamateur]

Hello all,

I am in need of some creative ideas for the following task: I need to compare hundreds of products to each other to find products that are similar/dis-similar based on the ingredients in their recipes. See below example products to illustrate my idea. In this product set I need some sort of algorithm/methodology that would programatically (quickly on a large scale) identify apple and cherry pie products as similar because there is only a one ingredient difference in their respective recipes and lasagna may be similar to other products but not with apple or cherry pie. To clarify, what I am not looking for is a match, deduplicate, list compare or anything like that.

Thoughts??? As always, any help is appreciated.

• Product A is a cherry pie, which contains sugar, butter, flour and cherries (the exact same recipe as apple pie but with cherries instead of apples)
• Product B is a apple pie, which contains sugar, butter, flour and applies (the exact same recipe as cherry pie but with applies instead of cherries)
• Product C is lasagna, which contains pasta, meat, tomato sauce, basil, etc

 

[offthelip]

I think this would be quite easy with a double loop in vba. Assuming that the product list and ingredients in in one worksheet. load the whole worksheet into a variant array, create an output array withthe same number of rows as the list of product, then create an outloop than runs down the whole product list. first count the number of ingredients in the the current product, then loop round all the other products and compare the ingredients on each product to the current product, if there is only one product different then add the name of that to the output array in the same row and the current product name. , keep searching and adding more similar products in aother columns, until the end of the list. Then the loop goes onto the next "current product"

 

[Roderick_E]

These kind of things come down to structure. How you structure the data will better allow for the comparison.
If it were as the example implies, just 3 rows of strings (like sentences), it would be difficult.
However, if each row had enough columns to contain X amount of ingredients; 1 ingredient in each column, you could then write a macro that grouped similar products together by common ingredients.

 

[automationamateur]

Ok, I like this idea. This will push my VB capabilities but I will research. Thanks!

 

[hiker95]

automationamateur,

Would it be possible to see your actual raw data workbook/worksheets, and, what the results (manually formatted by you) should look like?

You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com