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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

mole999

Moderator
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
6,007
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.

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Account: ABC Company</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Account: ABC Company</td><td style=";"></td><td style="text-align: right;;"></td><td style=";">Sales Director 1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Sales Director 1</td><td style=";"></td><td style="text-align: right;;"></td><td style=";">Community 1</td><td style=";">Account: ABC Company Community 1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Community 1</td><td style=";">Account: ABC Company Community 1</td><td style="text-align: right;;"></td><td style=";">Community 2</td><td style=";">Account: ABC Company Community 2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Community 2</td><td style=";">Account: ABC Company Community 2</td><td style="text-align: right;;"></td><td style=";">Community 3</td><td style=";">Account: ABC Company Community 3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Community 3</td><td style=";">Account: ABC Company Community 3</td><td style="text-align: right;;"></td><td style=";">Community 4</td><td style=";">Account: ABC Company Community 4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Community 4</td><td style=";">Account: ABC Company Community 4</td><td style="text-align: right;;"></td><td style=";">Account: XYZ Company</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Account: XYZ Company</td><td style=";"></td><td style="text-align: right;;"></td><td style=";">Sales Director 2</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Sales Director 2</td><td style=";"></td><td style="text-align: right;;"></td><td style=";">Community 1</td><td style=";">Account: XYZ Company Community 1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Community 1</td><td style=";">Account: XYZ Company Community 1</td><td style="text-align: right;;"></td><td style=";">Community 2</td><td style=";">Account: XYZ Company Community 2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Community 2</td><td style=";">Account: XYZ Company Community 2</td><td style="text-align: right;;"></td><td style=";">Community 3</td><td style=";">Account: XYZ Company Community 3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Community 3</td><td style=";">Account: XYZ Company Community 3</td><td style="text-align: right;;"></td><td style=";">Community 4</td><td style=";">Account: XYZ Company Community 4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Community 4</td><td style=";">Account: XYZ Company Community 4</td><td style="text-align: right;;"></td><td style=";">Community 5</td><td style=";">Account: XYZ Company Community 5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Community 5</td><td style=";">Account: XYZ Company Community 5</td><td style="text-align: right;;"></td><td style=";">Community 6</td><td style=";">Account: XYZ Company Community 6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">Community 6</td><td style=";">Account: XYZ Company Community 6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E3</th><td style="text-align:left">=IF(<font color="Blue">OR(<font color="Red">LEFT(<font color="Green">D3,1</font>)={"A","S"}</font>),"",IF(<font color="Red">E2="",OFFSET(<font color="Green">E3,-2,-1</font>)&" "&D3,LEFT(<font color="Green">E2,LEN(<font color="Purple">E2</font>)-LEN(<font color="Purple">OFFSET(<font color="Teal">E3,-1,-1</font>)</font>)</font>)&D3</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=IF(<font color="Blue">OR(<font color="Red">LEFT(<font color="Green">A2,1</font>)={"A","S"}</font>),"",IF(<font color="Red">B1="",OFFSET(<font color="Green">B2,-2,-1</font>)&" "&A2,LEFT(<font color="Green">B1,LEN(<font color="Purple">B1</font>)-LEN(<font color="Purple">OFFSET(<font color="Teal">B2,-1,-1</font>)</font>)</font>)&A2</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
You're welcome, welcome to the forum.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,042
Messages
5,639,731
Members
417,108
Latest member
Thein Than

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