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.
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!