+ Reply to Thread
Results 1 to 12 of 12

Need to improve speed of concatenation macro

  1. #1
    Registered User
    Join Date
    01-14-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    34

    Need to improve speed of concatenation macro

    Hi,
    I’ve got two columns, column A and B. I need to concatenate the data in column B, for which the data in column A is the same. Please see the spreadsheet for a visual explanation.
    The macro shown below works, however, I have tens of thousands of rows of data, and would like to improve the speed at which this macro works.
    I believe the way to do this is to create arrays, instead of row by row, but I don’t know how to do this.
    Any help is much appreciated. Thank you.

    Sub Combine_Programs()
    Dim section As Long, concattxt As String, lastpos As Long


    section = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    contattxt = ""
    lastpos = 2

    For i = 2 To section
    concattxt = Sheets("Sheet1").Range("B" & i).Value

    For j = i + 1 To section
    If Sheets("Sheet1").Range("A" & i).Value = Sheets("Sheet1").Range("A" & j).Value Then
    concattxt = concattxt & ", " & Sheets("Sheet1").Range("B" & j).Value
    i = j
    End If
    Next

    Sheets("sheet1").Range("I" & lastpos).Value = concattxt
    lastpos = lastpos + 1
    concattxt = ""
    Sheets("sheet1").Cells(1, 4) = i
    Next
    End Sub
    Attached Files Attached Files

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Need to improve speed of concatenation macro

    Try this.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    01-14-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    34

    Re: Need to improve speed of concatenation macro

    This is awesome Norie!! Thank you. One more question...is there an easy way to do the same thing, except transpose into separate columns, instead of concatenate into one?

    Thanks.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Need to improve speed of concatenation macro

    Like this?
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    01-14-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    34

    Re: Need to improve speed of concatenation macro

    That's perfect!!!!!!!!! Thank you so much for helping!!!! Much appreciated!

  6. #6
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,520

    Re: Need to improve speed of concatenation macro

    Here's another that dosent require Column A to be sorted

    Please Login or Register  to view this content.
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  7. #7
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,520

    Re: Need to improve speed of concatenation macro

    Here's another that dosent require Column A to be sorted

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    01-14-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    34

    Re: Need to improve speed of concatenation macro

    Mike, This program is even better...
    Another question for you, I need to do the same process for some other data, except instead of transposing into separate cells, I need to concatenate it into a single cell, for each row. Is there a quick way to do that with your code?
    Thanks.

  9. #9
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,520

    Re: Need to improve speed of concatenation macro

    Maybe this

    Please Login or Register  to view this content.
    Last edited by mike7952; 02-01-2013 at 11:07 AM.

  10. #10
    Registered User
    Join Date
    01-14-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    34

    Re: Need to improve speed of concatenation macro

    Wow, you're fast! I'm getting an error with the line

    .Offset(, 1).Value = .Item(e)

    Not sure how to fix it.

  11. #11
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,520

    Re: Need to improve speed of concatenation macro

    yea sorry I edited the code in post #9. Try it again.

  12. #12
    Registered User
    Join Date
    01-14-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    34

    Re: Need to improve speed of concatenation macro

    WOW!!! Thank you so much!! I appreciate your help and time!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1