A new twist on concatenation effort

matchmole

New Member
Joined
Jun 18, 2002
Messages
26
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
Joined
Mar 17, 2008
Messages
1,874
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
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

Sub AddAccountDescriptions()
  
  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
Joined
Oct 19, 2006
Messages
567
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
Joined
Oct 19, 2006
Messages
567
ooh i guess i had not read the post correctly ....

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
Joined
Jun 18, 2002
Messages
26

ADVERTISEMENT

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
Joined
Mar 17, 2008
Messages
1,874
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
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
Joined
Jun 18, 2002
Messages
26
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
 
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,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.
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