Concatenate by criteria

[imported_unknown]

2002_09_30 - Simon.xls
	A	B	C	D
1	Projects	Notes	Summary	Report
2	Project1	Builtairplane	Project1	Builtairplance;Buildboat
3	Project2	Drankbeer	Project2	Drankbeer;Wentbowling
4	Project1	Buildboat
5	Project2	Wentbowling
Sheet1

Hi All,

I'd like to know how to concatenate cells that meet a specific criteria. In the example above, I want to create report that shows the notes for each project.

Thanks for any tips,

Simon
This message was edited by sitary on 2002-10-04 15:28

 

[Aladin Akyurek]

In D2 array-enter & copy down:

=SUBSTITUTE(MCONCAT(IF($A$2:$A$5=C2,"; "&$B$2:$B$5,"")),"; ","",1)

To array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.

Moreover, MCONCAT is part of the morefunc.xll add-in, which is downloadable from:

http://longre.free.fr/english/index.html

See...

aaConditionalConcat Sitary.xls
	A	B	C	D
1	Projects	Notes	Summary	Report
2	Project1	Builtairplane	Project1	Builtairplane;Buildboat
3	Project2	Drankbeer	Project2	Drankbeer;Wentbowling
4	Project1	Buildboat
5	Project2	Wentbowling
Sheet1

 

[imported_unknown]

[No message]

 

[imported_unknown]

Hi Aladin,

That works great - thanks very much.

Simon