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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Leith Ross

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
Office Version
2010, 2007
Platform
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
2010, 2007
Platform
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,799
Messages
5,513,491
Members
408,954
Latest member
Tiaame

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top