If/then

[Wynn]

7 cat unique
7 dog dupe

for D1:
=If(C3="dupe",B1&" ; "&B2)

consequentally text in D1 = cat;dog

But I am at a loss for...

9 cat unique
9 dog dupe
9 goat dupe

I want D1 text = cat;dog;goat

Any suggestions?

P.S. unique/dupe refer to column A.

 

[Frank Kabel]

Hi
first download the free add-in morefunc.xll
(http://longre.free.fr/english). It includes the function MCONCAT to
concatenate an array of strings.
In D1 enter the following formula
=IF(C1="unique",MCONCAT(OFFSET(B1,0,0,IF(ISNA(MATCH("unique",C2:$C$1000
,0)),COUNTA(C2:$C$1000)+1,MATCH("unique",C2:$C$1000,0))),"-"),";")
and copy this formula down