Combining cells

amberdk

New Member
Joined
Sep 22, 2017
Messages
5
I need to concatenate some cells, but this is tricky and I'm not sure how to do it. Example of data below:

Account: ABC Company
Sales Director 1
Community 1
Community 2
Community 3
Community 4
Account: XYZ Company
Sales Director 2
Community 1
Community 2
Community 3
Community 4

<tbody>
</tbody>
Community 5
Community 6

<tbody>
</tbody>


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.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

mole999

Well-known Member
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
1 col or row per company, then you can $A$1 &" "&$A2 and drag down
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDE
1Account: ABC Company
2Account: ABC Company Sales Director 1
3Sales Director 1Community 1Account: ABC Company Community 1
4Community 1Account: ABC Company Community 1Community 2Account: ABC Company Community 2
5Community 2Account: ABC Company Community 2Community 3Account: ABC Company Community 3
6Community 3Account: ABC Company Community 3Community 4Account: ABC Company Community 4
7Community 4Account: ABC Company Community 4Account: XYZ Company
8Account: XYZ CompanySales Director 2
9Sales Director 2Community 1Account: XYZ Company Community 1
10Community 1Account: XYZ Company Community 1Community 2Account: XYZ Company Community 2
11Community 2Account: XYZ Company Community 2Community 3Account: XYZ Company Community 3
12Community 3Account: XYZ Company Community 3Community 4Account: XYZ Company Community 4
13Community 4Account: XYZ Company Community 4Community 5Account: XYZ Company Community 5
14Community 5Account: XYZ Company Community 5Community 6Account: XYZ Company Community 6
15Community 6Account: XYZ Company Community 6
Sheet1
Cell Formulas
RangeFormula
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))
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
You're welcome, welcome to the forum.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,073
Messages
5,857,197
Members
431,862
Latest member
wiz of az

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top