How could a formula return multiple values as concatenated text ?

E

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
 
P

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
 
E

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 -
Click to expand...

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
 
Top