# A new twist on concatenation effort

#### matchmole

##### New Member
Hi,
I have two columns of data. First is an account code with 22,268 rows. Second is a string of numbers which I need to "append" to the account codes. Problem is, the second field has only eight rows. So I need to have the account code in col 1 row 1 concatenate with each of the entries in col 2... then have the account code in col 1 row 2 concatenate with each of the entries in col 2... etc.

In the samples below, Account code is in col A... String(s) to be added are in col B. Then in col C I need for each code in col A to be joined with all of the strings in col B.

Account Code String(s) to be added to each Account Code
001-001001-001234- 401260 Benefits Retirement 1 2
001-001001-002345- 401270 Benefits Retirement 1 2
001-001001-003456- 401280 Benefits Retirement 1 2
001-001001-004567- 750400 Benefits Retirement 1 2
001-001001-005678- 410000 Benefits Retirement 1 2
001-001001-006789- 420000 Benefits Retirement 1 2
002-002001-001234- 430000 Benefits Retirement 1 2
002-002001-002345- 440000 Benefits Retirement 1 2
002-002001-003456- 450000 Benefits Retirement 1 2
002-002001-004567- 460000 Benefits Retirement 1 2
002-002001-005678-
002-002001-006543-
002-002001-006432-
002-002001-006321-
002-002001-005432-

So I would like to end up with this result:
001-001001-001234-401260
001-001001-001234-401270
001-001001-001234-401280
001-001001-001234-750400
001-001001-001234-410000 ... and on and on until each account code in col A has been combined with every string in col B. Does that make sense?

Peter was kind enough to send code that does the job as I originally needed. Now they tell me that I need to add description(s) to this project... to make the result look like this:

001-001001-001234-401260 Benefits Retirement 1 2
001-001001-001234-401270 Benefits Retirement 1 2
and so on. So it is really an expansion of Peter's code (copied below) that I need now to emcompass these extra fields/columns that must be a part of my results.

Here is the code that Peter generated previously:

Sub concat()
Dim LR As Long, i As Long, j As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
For j = 1 To 8
Range("C" & Rows.Count).End(xlUp).Offset(1).Value = Range("A" & i).Value & Range("B" & j).Value
Next j
Next i
End Sub

I wish I were smart enough to do this myself... but I'm not. Thanks for your help.

Ken

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### Leith Ross

##### Well-known Member
Hello matchmole,

This code is longer than Peter's because of some changes. You have a lot of data now, and it will probably get larger. I felt processing speed might become a concern. The macro uses an array to speed up the process of copying the concatenated data into column "C'.

Both columns "A" and "B" are sized automatically. In column "A", if the last row is less than the starting row then macro exits because there is no data. Column "B" will not exit the macro under the same conditions because it is used as a loop control value.

Code:
``````'Written: August 02, 2010
'Author:  Leith Ross

Dim Acct As Variant
Dim AcctExt As Variant
Dim Descriptions As Variant
Dim N As Long
Dim Rng As Range
Dim RngEnd As Range
Dim Wks As Worksheet

'Worksheet where the accounts are located
Set Wks = Worksheets("Sheet1")

'Find the last account in column "A"
Set Rng = Wks.Range("A1")
Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
If RngEnd.Row < Rng.Row Then Exit Sub Else Set Rng = Wks.Range(Rng, RngEnd)

'Find the last description in column "B"
Set Descriptions = Wks.Range("B1")
Set RngEnd = Wks.Cells(Rows.Count, Descriptions.Column).End(xlUp)
Descriptions = Wks.Range(Descriptions, RngEnd).Value

'Resize the Account Extended array to the number of rows needed
ReDim AcctExt(1 To Rng.Rows.Count * UBound(Descriptions), 1 To 1)

'Save the account number and its description in the Account Extended array
For Each Acct In Rng
For Each Desc In Descriptions
N = N + 1
AcctExt(N, 1) = Acct & Desc
Next Desc
Next Acct

'Block write the array to column "C" after expanding the column's rows to match the array
Wks.Range("C1").Resize(N, 1).Value = AcctExt

End Sub``````
Sincerely,
Leith Ross

#### hsk

##### Well-known Member
Are you sure you want a macro for that .....
Try this formula ...

=CONCATENATE(INDIRECT("A"&ROUNDUP(ROW(1:1)/10,0)),INDIRECT("B"&IF((ROW(1:1)-((ROUNDDOWN(ROW(1:1)/10,0))*10))>0,ROW(1:1)-((ROUNDDOWN(ROW(1:1)/10,0))*10),10)))

Paste this in cell C1 and copy it till rows 10 times rows in Col A.
e.g if Col A has 50 rows copy the formula till 500 rows ....

U should get what you want ....

This is just one way ... the formula can be made 1000 time better ... Experts pl try !!!!

#### hsk

##### Well-known Member

U have 22268 rows in Col A .... and 10 rows in Col B / C ... so that makes total 222680 combinations ... is that correct ?

IF so then probably the macro is better option !!

#### matchmole

##### New Member

Leith,

Your code works great for concatenating A & B into C. But maybe I didn't explain very well what we need to accomplish. I'll try again here:

Col A Col B Col C Col D Col E Col F
Acct Nom Desc Category TypBal PostType

The information in columns B thru F "already go together" which is to say that info is consolidated into those columns and for the sake of writing code they could be considered "one entity". But the information in column A is:

1) A partial account # that needs to "join with" each nom in column B along with that nom's desc in column C along with that nom's category in column D along with that nom's typical balance in column E along with that nom's posting type in column F.

2) Column A contains more rows of data (~22,000) than do columns B thru F (~1200)

Does that help make it more understandable?

Thanks,
Ken

#### Leith Ross

##### Well-known Member
Hello matchmole,

Sorry about the misunderstanding. Okay, you need the entire row's contents concatenated. I can change the macro to do that.

Sincerely,
Leith Ross

#### matchmole

##### New Member
Leith,

Thanks very much for your effort and assistance. I look forward to using your new code when you have time to create it.

Ken

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.

1,168,113
Messages
5,857,453
Members
431,880
Latest member
kar2rost

### 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.

### Which adblocker are you using?

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

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