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.
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,339
Office Version
2019, 2016, 2013
Platform
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
5,146
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 />
 

Watch MrExcel Video

Forum statistics

Threads
1,099,465
Messages
5,468,791
Members
406,607
Latest member
mario antonio

This Week's Hot Topics

Top