comparing multiple dishes with a wide range of ingredients


I work for an industrial food company.
I’ve been tasked with finding out what “dishes” contain the same or more “ingredients” (amount doesn’t matter) as any of the other dishes.
Example: If dish1 has ingredients A and B, and dish2 has A, B and C, I need a positive result from dish1->dish2, but a negative from dish2->dish1 (as dish1 doesn’t contain C)

I have a spreadsheet with dishes at the top of each column, and ingredients down column 1. The sheet is filled in with the recipes amounts of ingredients for each dish.

We have 130 different ingredients, and approx 50 different dishes (but new ones come in once in a while, so I also need an easy way to add the newcomer).

For each of the 50 dishes I need a list of dishes which contain the same or more ingredients.

My only way to solve this is to manually compare each dish to the other 49, and do that for all of them, but I don’t like manual work :stuck_out_tongue:

Thanks in advance.

Hello @hmthyssen

Have you ever thought to build a macro that would scan the recipes and the ingredients to build the report ?

Kind regards, Michel


not to long ago i wrote a macro which might help.

It should give you a list of ingredients and tell you which dishs require them

Simple Example:

image description

You can find the macro and a bit more description here:

Hope that helps.