count of dishes that contain specific ingredients

[dmalhotr]

Hi,

I have a table in excel where the first column is dishes and every
subsequent column is ingredients
------------------------------------------------------
dishes | ingredients | ingredients | ingredients
--------------------------------------------------------------------
dish 1 apple corn meat
dish 2 salt apple fish
dish 3 meat grain corn
dish x w y z
--------------------------------------------------------

I would like to translate the result into another excel file in the
following format:

item | # of dishes that is using ingredient | dish 1 | dish 2 | dish
3 | dish x
-----------------------------------------------------------------------------------------------------------
apple 2 x x
corn 2 x
x
salt 1 x
meat 2 x
x
fish 1 x

Is there a way to do this without using VBA code?

Thanks

 

[chelovik]

Hi
You could use an array formula to do this quite easily ... it would require
you have a list of the ingredients already built on another sheet or area of
your workbook ...
The formula would be something like this:
{=COUNT(IF($B$4:$D$6=A9,1))}
where B46 is the area with the dishes ... and A9 is the ingredient (eg,
apple.)
I'll send you my workbook to have a look at ... if you don't get it, write
to me at (e-mail address removed)
- Paul