What formula could you place in the "Total Inventory" column to return
the results below ?

Fruit In Stock Total Inventory
Apple Yes Apple
Orange No Apple
Peach Yes Apple,Peach
Orange Yes Apple,Peach,Orange
Peach No Apple,Orange

Put this in C2:

=IF(A2="","",A2)

and put this in C3:

=IF(B3="Yes",IF(NOT(ISNUMBER(SEARCH(A3,C2))),C2&","&A3,C2),IF(AND(B3="No",ISNUMBER(SEARCH(A3,C2))),SUBSTITUTE(C2,IF(A3=A
$2,A3&",",","&A3),""),C2))


Copy the formula from C3 down the column as required. It works for
your sample data, as well as if A6 is either Apple or Orange, but may
need some tweaking on your real data.

Hope this helps.

Pete

On Aug 21, 10:16*pm, exceluser <ifmcqy7aias...@yahoo.com> wrote:
> What formula could you place in the "Total Inventory" column to return
> the results below ?
>
> Fruit * In Stock * * * *Total Inventory
> Apple * Yes * * Apple
> Orange *No * * *Apple
> Peach * Yes * * Apple,Peach
> Orange *Yes * * Apple,Peach,Orange
> Peach * No * * *Apple,Orange

On Aug 21, 8:33*pm, Pete_UK <pashu...@auditel.net> wrote:
> Put this in C2:
>
> =IF(A2="","",A2)
>
> and put this in C3:
>
> =IF(B3="Yes",IF(NOT(ISNUMBER(SEARCH(A3,C2))),C2&","&A3,C2),IF(AND(B3="No",I*SNUMBER(SEARCH(A3,C2))),SUBSTITUTE(C2,IF(A3=A
> $2,A3&",",","&A3),""),C2))
>
> Copy the formula from C3 down the column as required. It works for
> your sample data, as well as if A6 is either Apple or Orange, but may
> need some tweaking on your real data.
>
> Hope this helps.
>
> Pete
>
> On Aug 21, 10:16*pm, exceluser <ifmcqy7aias...@yahoo.com> wrote:
>
>
>
> > What formula could you place in the "Total Inventory" column to return
> > the results below ?
>
> > Fruit * In Stock * * * *Total Inventory
> > Apple * Yes * * Apple
> > Orange *No * * *Apple
> > Peach * Yes * * Apple,Peach
> > Orange *Yes * * Apple,Peach,Orange
> > Peach * No * * *Apple,Orange- Hide quoted text -
>
> - Show quoted text -

Pete,

That was it !

You're the man.

Although the formula changed quite a bit, using the SEARCH function
was what I needed to use.

Thank you very much.


Exceluser

You're welcome, and thanks for feeding back.

Pete

On Aug 26, 2:16*am, exceluser <ifmcqy7aias...@yahoo.com> wrote:
>
> Pete,
>
> * *That was it !
>
> * *You're the man.
>
> * *Although the formula changed quite a bit, using the SEARCH function
> was what I needed to use.
>
> * *Thank you very much.
>
> Exceluser