Merge Rows While Maintaining Unique Values

  • Hello,


    Please bear with me, I am new to ozgrid.


    I am trying to merge rows containing like data while concatenating the unique values in those rows.
    I am looking for a VBA-excel solution and have included an example excel file.


    In the example I would like to identify like values in column D (if the value is D is a match, then columns a through c will also match) and concatenate column E for the merged rows with a ";" delimiter.


    ie: (from the sample sheet)


    ColumnA ColumnB ColumnC ColumnD ColumnE
    CC Dis. Name Manager User ID Group
    2319 Timmons Johnson ECCPTTT 1;2;3


    Thank you in advance for your time and assistance.

  • Re: Merge Rows While Maintaining Unique Values


    Here is one approach. It puts the results on sheet2

  • Re: Merge Rows While Maintaining Unique Values


    Stephen,


    Thank you for the response! However, upon execution, it is returning a Run-Time Error '13' Type Mismatch on the following line.


    v = Sheet1.Range("A1").CurrentRegion.Value

  • Re: Merge Rows While Maintaining Unique Values


    No, I'm running it on the test file that I uploaded. I will inevitably need to run it on a different file that is formatted the same, only a much larger file.

  • Re: Merge Rows While Maintaining Unique Values


    I do not.
    I copied your code directly in my "PERSONAL" workbook (where all of my macros are saved) and run it from there on the open Workbook.
    I've tried the code in both the original Test file that I uploaded here and have copied it into the code of the macro where I truly need it.


    I receive the same Run-Time Error '13' Type Mismatch on v = Sheet1.Range("A1").CurrentRegion.Value

    If I turn "Option Explicit" on, I receive Compile Error: Variable not defined on Sheet2.Range("A1").Resize(k, 5) = vOut (Specifically highlighting "Sheet2")
    I should note that this is the case in both files.


    I appreciate your assistance and patience thus far and any continued assistance you can provide. I am fairly new to VBA and macros (as in, my experience is limited to the last few weeks).
    I've successfully written several macros over the last two weeks, but this particular file required knowledge that is currently over my head (as is the code that you provided), hence my request here.

  • Re: Merge Rows While Maintaining Unique Values


    The Macro is creating a new workbook with "Sheets1", "Sheets2", and "Sheets3". Below is the code in its entirety...


    Currently getting...
    "Run Time Error 13: Type Mismatch" on the line below
    v = Sheet1.Range("A1").CurrentRegion.Value


    "Compile Error: Variable not defined" on the line below.
    Sheet2.Range("A1").Resize(k, 5) = vOut


    I realized that in the test file I uploaded Column E was all numeric, in the true file, Column E is populated with text. I don't know if this is causing any of the issues?


  • Re: Merge Rows While Maintaining Unique Values


    The code name is different from the sheet name though http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm Your new sheets may have not have sheet2 code name. The other thing is that you said earlier that you didn't have any other workbooks open, but it looks like you do and so you should add a workbook reference to make sure the code is pointing to the right file. Try

    Code
    Thisworkbook.sheets("Sheet2").Range("A1").Resize(k, 5) = vOut
  • Re: Merge Rows While Maintaining Unique Values


    Thank you.


    I also just found the code name to be different from the sheet name and was attempting to work around that. I will try the code as you suggested above.


    However, I am getting stopped by this error before I can even get there.


    "Run Time Error 13: Type Mismatch" on the line below
    v = Sheet1.Range("A1").CurrentRegion.Value

  • Re: Merge Rows While Maintaining Unique Values


    Code
    Dim v()

    should be

    Code
    Dim v

    although I don't see why that should error. Change the sheet1 to a sheet name also, and add a workbook qualifier. Don't really have time now, but the rest of your code could probably be pruned a little,

  • Re: Merge Rows While Maintaining Unique Values


    That worked, but now getting the same error on the next line.


    "Run Time Error 13: Type Mismatch" on the line below

    Code
    ReDim vOut(1 To UBound(v, 1), 1 To 5)


    I'm sure the rest of my code could use some cleaning up and would be very appreciative of any pointers, but as long as it works, I'm not terribly worried about it at this time.

  • Re: Merge Rows While Maintaining Unique Values


    Happily, but the macro is designed to open 19 separate workbook and merge them into a single new workbook.


    Would you like me to attach them all? Or run it through the merge process and comment out the rest of the code (aside from the de-duplication section) before sending?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!