How could a formula return multiple values as concatenated text ?

[exceluser]

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

 

[Pete_UK]

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

 

[exceluser]

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




- 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

 

[Pete_UK]

You're welcome, and thanks for feeding back.

Pete