0

I have an excel spreadsheet with one column for ingredients and one column for recipes. (see on the left in the picture below). I would like to create a matrix with all ingredients (as seen in the picture on the right) that shows how many times each pair of ingredients appears in the same recipes.

input and wanted output

So far I tried it with combinations of VLOOKUP and COUNTIFS but I did not get a sufficient solution.

The command I am looking for should be something like: "For every time ingredient-x (Gin) appears in a recipe, check which other ingredients appear in the same recipe. And count these co-appearances in the matrix."

My limited knowledge tells me that this is only possible with an algorithm written in VBA because I'd have to tell it to "for every ingredient count home often every other ingredient appears in the same recipe.

I would appreciate your help, and since this is my first question on Superuser please don't hesitate to give me feedback on how to improve my asking!

1
  • Welcome to SuperUser. You can put VLOOKUP and COUNTIFS inside code samples to make them stand out. Nov 19, 2018 at 10:58

1 Answer 1

0

EDIT: A better solution that doesn't require an extra column

You can use the functionCOUNTIFS to check if your first range is equal to something and if your second range is equal to something.

I used the function: =COUNTIFS(B3:B8, "Tonic Water", C3:C8, "Gin") To count how many combinations of Tonic Water + Gin where in the two ranges.

You could also see the function like this: =COUNTIFS(RANGE1, RECIPE, RANGE2, INGREDIENT)

Here is an image explaining it a bit better:

countifs

I checked your "input and wanted output" image and the data in the output table doesn't match up with whats in the input ranges. However if I look at your question it seems that my answer will work for that.

EDIT: My comment answer:

Put the ingredients on the left side and the ingredients at the top. Then use COUNTIFS to check how many times an ingredient is in a recipe. From this table you can easily check if and what ingredients share a recipe.

Image example: enter image description here

In the image you can see Ginger Ale and Vodka both share the recipe Moscow Mule

3
  • Thank you, but sadly this is not the solution I was looking for. Now it counts how many times the recipe name matches the name of an ingredient. How many times do two ingredients appear with the same recipe name in the column to their left? My guess that I'll have to use excel-vba because has to repeat one process (how many times do two ingredients appear in the same recipe) over and over again.
    – djn
    Nov 19, 2018 at 12:44
  • You can make an extra table where you put all the Recipes on the left and then all the Ingredients on the top and then use COUNTIF to check if an ingredient is in a recipe. From this table you might be able to extract the information you need. Nov 19, 2018 at 15:01
  • @djn Glad it worked for you. Don't forget to accept this answer if it solved your question. Nov 21, 2018 at 13:40

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .