# Concatenate 3 dynamic ranges?

#### Meinders

##### New Member
Hi....

Got a random question, like everyone else

I'm trying to concatenate 3 dynamically named ranges and output them on the same worksheet.

I have data in Column A which is named Mode, I have data in Column B named Area, and data in Column C which is named Test.

I want to concatenate all 3 and output all possible combinations.

For example:

A B C
SP San F Physics
Coop Detroit Scoring
MP Asia SFX

These are just examples.....I'm looking for a way to create the output:

SP - San F - Physics
SP - San F - Scoring
SP - San F - SFX
SP - Detroit - Physics
SP - Detroit - Scoring
SP - Detroit - SFX
SP - Asia - Physics
SP - Asia - Scoring
SP - Asia - SFX
Coop - San F - Physics
.....

and on and on, you get the idea. In this instance, I would be looking for a list of 27 combinations, I believe. However, each of those ranges are dynamic, and I would like to be able to add to that list without being required to constantly change code.

Many thanks,
Brian

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### mvptomlinson

##### Well-known Member
Hi Brian, perhaps try:

Code:
``````Option Explicit
Option Base 1

Sub reOrg()
Dim arr1, arr2, arr3
Dim i As Long, j As Long, k As Long, x As Long

arr1 = Range("colA")
arr2 = Range("colB")
arr3 = Range("colC")
x = 1

Application.ScreenUpdating = False
For i = 1 To UBound(arr1)
For j = 1 To UBound(arr2)
For k = 1 To UBound(arr3)
Range("E" & x) = arr1(i, 1) & " - " & arr2(j, 1) & " - " & arr3(k, 1)
x = x + 1
Next k
Next j
Next i
Application.ScreenUpdating = True
End Sub``````
"colA", "colB" and "colC" are the three named ranges I used - change these to your named ranges of course. The data is output to column E starting in E1 (which can also be changed).

#### Meinders

##### New Member
Worked like a charm....

Simply amazing what smart people can make excel do.

#### mikerickson

##### MrExcel MVP
Here's a non-macro solution that accommodates different numbers of entries in each column.
Select a cell in row 1 and define three names. The use of relative addressing makes cell selection when defining the ranges important.

Name: FirstString
RefersTo: =INDEX(Sheet1!\$A:\$A,MOD(ROW(Sheet1!\$A1),COUNTA(Sheet1!\$A:\$A))+1)

Name: SecondString
RefersTo: =INDEX(Sheet1!\$B:\$B,INT(MOD(ROW(Sheet1!\$A1)/COUNTA(Sheet1!\$A:\$A),COUNTA(Sheet1!\$B:\$B)))+1,1)

Name: ThirdString
RefersTo: =INDEX(Sheet1!\$C:\$C,INT(MOD(ROW(Sheet1!\$A1)/COUNTA(Sheet1!\$A:\$A)/COUNTA(Sheet1!\$B:\$B),COUNTA(Sheet1!\$C:\$C)))+1,1)

Then in E1 put
=FirstString &" - "& SecondString &" - "& ThirdString
and drag down.
<table border=1 cellspacing=0>
<tr align="center" bgcolor=#A0A0A0><td width=25> <td width=25><b>A</b><td width=25><b>B</b><td width=25><b>C</b><td width=25><b>D</b><td width=225><b>E</b></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>1</b><td align="left" bgcolor=#FFFFFF>SP<td align="left" bgcolor=#FFFFFF>San F<td align="left" bgcolor=#FFFFFF>Physics<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>Coop - San F - Physics</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>2</b><td align="left" bgcolor=#FFFFFF>Coop<td align="left" bgcolor=#FFFFFF>Detroit<td align="left" bgcolor=#FFFFFF>Scoring<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>MP - San F - Physics</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>3</b><td align="left" bgcolor=#FFFFFF>MP<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>SFX<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>SP - Detroit - Physics</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>4</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>cat<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>Coop - Detroit - Physics</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>5</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>MP - Detroit - Physics</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>6</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>SP - San F - Scoring</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>7</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>Coop - San F - Scoring</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>8</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>MP - San F - Scoring</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>9</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>SP - Detroit - Scoring</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>10</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>Coop - Detroit - Scoring</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>11</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>MP - Detroit - Scoring</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>12</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>SP - San F - SFX</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>13</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>Coop - San F - SFX</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>14</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>MP - San F - SFX</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>15</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>SP - Detroit - SFX</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>16</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>Coop - Detroit - SFX</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>17</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>MP - Detroit - SFX</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>18</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>SP - San F - cat</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>19</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>Coop - San F - cat</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>20</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>MP - San F - cat</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>21</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>SP - Detroit - cat</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>22</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>Coop - Detroit - cat</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>23</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>MP - Detroit - cat</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>24</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>SP - San F - Physics</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>25</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#CCFFFF>repeat<td align="left" bgcolor=#CCFFFF>Coop - San F - Physics</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>26</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#CCFFFF><td align="left" bgcolor=#CCFFFF>MP - San F - Physics</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>27</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#CCFFFF><td align="left" bgcolor=#CCFFFF>SP - Detroit - Physics</tr>
</table>

Last edited:

1,106,926
Messages
5,514,206
Members
408,990
Latest member
fresse68