A new twist on concatenation effort

[matchmole]

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

 

[Leith Ross]

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.

'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]

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]

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]

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]

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]

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