Combining cells

[amberdk]

I need to concatenate some cells, but this is tricky and I'm not sure how to do it. Example of data below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Account: ABC Company[/TD]
[/TR]
[TR]
[TD] Sales Director 1[/TD]
[/TR]
[TR]
[TD] Community 1 [/TD]
[/TR]
[TR]
[TD] Community 2[/TD]
[/TR]
[TR]
[TD] Community 3[/TD]
[/TR]
[TR]
[TD] Community 4[/TD]
[/TR]
[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]Account: XYZ Company[/TD]
[/TR]
[TR]
[TD] Sales Director 2[/TD]
[/TR]
[TR]
[TD] Community 1 [/TD]
[/TR]
[TR]
[TD] Community 2[/TD]
[/TR]
[TR]
[TD] Community 3[/TD]
[/TR]
[TR]
[TD] Community 4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD] Community 5 [/TD]
[/TR]
[TR]
[TD] Community 6[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


This comes from a pivot, but there are a a few hundred "Companies" and then each company has a various amount of communities under it. What I need is a concatenation of Company and Community, so I would need for example "Account: ABC Company Community 1" for every single community in the list.

 

[mole999]

1 col or row per company, then you can $A$1 &" "&$A2 and drag down

 

[jtakw]

Hi,

Based on your sample data, if you can add a dummy row on top, use B2 formula copied down, without dummy row, put formula in 3rd row as shown in E3 copied down.

Excel 2010
	A	B	C	D	E
1				Account: ABC Company
2	Account: ABC Company			Sales Director 1
3	Sales Director 1			Community 1	Account: ABC Company Community 1
4	Community 1	Account: ABC Company Community 1		Community 2	Account: ABC Company Community 2
5	Community 2	Account: ABC Company Community 2		Community 3	Account: ABC Company Community 3
6	Community 3	Account: ABC Company Community 3		Community 4	Account: ABC Company Community 4
7	Community 4	Account: ABC Company Community 4		Account: XYZ Company
8	Account: XYZ Company			Sales Director 2
9	Sales Director 2			Community 1	Account: XYZ Company Community 1
10	Community 1	Account: XYZ Company Community 1		Community 2	Account: XYZ Company Community 2
11	Community 2	Account: XYZ Company Community 2		Community 3	Account: XYZ Company Community 3
12	Community 3	Account: XYZ Company Community 3		Community 4	Account: XYZ Company Community 4
13	Community 4	Account: XYZ Company Community 4		Community 5	Account: XYZ Company Community 5
14	Community 5	Account: XYZ Company Community 5		Community 6	Account: XYZ Company Community 6
15	Community 6	Account: XYZ Company Community 6
Sheet1

Cell Formulas
Range	Formula
E3	=IF(OR(LEFT(D3,1)={"A","S"}),"",IF(E2="",OFFSET(E3,-2,-1)&" "&D3,LEFT(E2,LEN(E2)-LEN(OFFSET(E3,-1,-1)))&D3))
B2	=IF(OR(LEFT(A2,1)={"A","S"}),"",IF(B1="",OFFSET(B2,-2,-1)&" "&A2,LEFT(B1,LEN(B1)-LEN(OFFSET(B2,-1,-1)))&A2))

 

[amberdk]

Thank you! Works great!!

 

[jtakw]

You're welcome, welcome to the forum.