Combine Duplicate Rows but Concantenate One Column

wilson.ct3 · Mar 23, 2017

Hey Everyone,

I have a worksheet that I paste a dataset the row just under the table on the page. I then combine and remove duplicate rows. However, I added a additional column to the table (column AC), in which I would like to add notes. However when I combine duplicate rows, the newer row, blank cell overwrites it. I found this awesome macro to remove duplicate rows, but I cant for the life of me to also get it to concatenate the data from column AC. Any help would be hugely appreciated!

Code:

Option Explicit

Sub removeDupesKeepLast()
    Dim d As Long, dDQs As Object, ky As Variant
    Dim r As Long, c As Long, vVALs As Variant, vTMP As Variant


    'appTGGL bTGGL:=False   'uncomment this when you have finished debugging


    Set dDQs = CreateObject("Scripting.Dictionary")
    dDQs.comparemode = vbTextCompare


    'step 1 - bulk load the values
    With Worksheets("Master RFL Pipeline").Range("Table135")   'you should know what worksheet you are on
        With .Cells(1, 1).CurrentRegion 'block of data radiating out from A1
            With .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0) 'step off the header row
                vVALs = .Value  'use .Value2 if you do not have dates in unformatted cells
            End With
        End With
    End With


    'step 2 - build the dictionary
    ReDim vTMP(UBound(vVALs, 2) - 1)
    For r = LBound(vVALs, 1) To UBound(vVALs, 1)
        For c = LBound(vVALs, 2) To UBound(vVALs, 2)
            vTMP(c - 1) = vVALs(r, c)
        Next c
        dDQs.Item(vVALs(r, 1) & ChrW(8203)) = vTMP
    Next r


    'step 3 - put the de-duplicated values back into the array
    r = 0
    ReDim vVALs(1 To dDQs.Count, LBound(vVALs, 2) To UBound(vVALs, 2))
    For Each ky In dDQs
        r = r + 1
        vTMP = dDQs.Item(ky)
        For c = LBound(vTMP) To UBound(vTMP)
            vVALs(r, c + 1) = vTMP(c)
        Next c
    Next ky


    'step 4 - clear the destination; put the de-duplicated values back into the worksheet and reset .UsedRange
    With Worksheets("Master RFL Pipeline").Range("Table135")   'you should know what worksheet you are on
        With .Cells(1, 1).CurrentRegion 'block of data radiating out from A1
            With .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0) 'step off the header row
                .ClearContents  'retain formatting if it is there
                .Cells(1, 1).Resize(UBound(vVALs, 1), UBound(vVALs, 2)) = vVALs
            End With
        End With
        .UsedRange   'assert the UsedRange property (refreshes it)
    End With


    dDQs.RemoveAll: Set dDQs = Nothing


    appTGGL
End Sub


Public Sub appTGGL(Optional bTGGL As Boolean = True)
    With Application
        .ScreenUpdating = bTGGL
        .EnableEvents = bTGGL
        .DisplayAlerts = bTGGL
        .AutoRecover.Enabled = bTGGL   'no interruptions with an auto-save
        .Calculation = IIf(bTGGL, xlCalculationAutomatic, xlCalculationManual)
        .CutCopyMode = False
        .StatusBar = vbNullString
    End With
    Debug.Print Timer
End Sub

Combine Duplicate Rows but Concantenate One Column

wilson.ct3

New member