comparing multiple dishes with a wide range of ingredients

[hmthyssen]

Hello

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

Thanks in advance.
/Hans

[mgl]

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

[igorlius]

Hi,

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:

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

Open-ended data sorting

Hello, i think the following macro (see. end of this post) does what you want. Just put it into Tools → Macros → “Edit Macros” and you can call it from inside any cell like this: =PRIZES2NAMES("A1:C5") The first argument is the complete table selection (with the prizes in the first row) quoted as a string [image description] The result will be put into one cell and look lke this: [image description] Here the macro module: Function PRIZES2NAMES(data As String) Const sep = ";" Dim na…

Hope that helps.