How could a formula return multiple values as concatenated text ?

  • Thread starter Thread starter exceluser
  • Start date Start date
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
 
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
 
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
 
Back
Top