Combine text from multiple cells, Based on conditions

[dark_horizon]

Hello friends,


Following is a small block of data. I need to write a formula, such that


Output in B1


If A1="All Segments",

Combine A2, A3, A4, A5 (into B1).....till another "All Segments" is encountered

If A1<>"All Segments", return '0'

[pre]

X       A               	B
1	All Segments    	India, Outside India
2	India           	0
3	Outside India   	0
4	All Segments    	Formalin, Methanol, Siliguri
5	Formalin        	0
6	Methanol        	0
7	Siliguri        	0
8	All Segments    	Domestic, Exports
9	Domestic        	0
10	Exports         	0
11	All Segments
12	Cattle & Poultry Feed
13	Poultry
14	Sugar & Related Activities
15	Sugar Trading
16	All Segments
17	Insulation
18	Trading

[/pre]
Any help would be appreciated. Thanks in anticipation.

 

[Montrey]

Probably going to need a macro for this.

 

[Colin Legg]

You can do it okay if you're prepared to use a helper column.


In B1 put this formula and fill down:

[pre]

=IF(OR(A2="All Segments",B2=""),A1&"",IF(OR(A1={"All Segments",""}),B2,A1&", "&B2))
In C1 put this formula and fill down:

=IF(A1="All Segments",B1,0)

[/pre]
Then hide column B if you don't want to see it.


The problem with Excel in this case is that its string worksheet functions aren't very well equipped: CONCATENATE() and the & operator can't work with arrays, so you're either forced into using a couple of formulas or, as Montrey said, write a VBA UDF to do it.

 

[dark_horizon]

Excellent Sir. That works like a charm. Am fine with helper columns.


Thanks a lot for the help.


Query resolved.

 

[Montrey]

Great Job Colin