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

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 Removed)
- Paul


"(E-Mail Removed)" wrote:

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