EXCEL - Merge duplicate and append unique Cells

Posted on 2014-08-12
Last Modified: 2014-08-13
I have a listing of thousands of lines that are duplicate base off some unique primary cells.  There are some other cells that are unique.  I would like to append/combine these cells.  Is this possible?

I am attaching an example with the raw data and final output.  Not sure if Excel can do this or some VBA script can?

Question by:holemania

    Author Comment

    The primary key/column would be A and B in this case.  A and B will be the same, but column C and D changes.  I want to remove duplicate and then just append column C and D with a comma separating them.
    LVL 18

    Accepted Solution

    Option Explicit
    Sub kTest()
        Dim dic As Object, i As Long, s As String
        Dim k, kk(), n As Long, c As Long
        Const SheetName As String = "Sheet2"      '<<<<<< adjust the sheet name
        Set dic = CreateObject("scripting.dictionary")
            dic.comparemode = 1
        With ThisWorkbook.Worksheets(SheetName)
            k = .Range("a1").CurrentRegion.Value2
            ReDim kk(1 To UBound(k, 1), 1 To UBound(k, 2))
            For i = 2 To UBound(k, 1)
                s = vbNullString
                s = k(i, 1) & "|" & k(i, 2) 'col a & b
                If Len(s) Then
                    If Not dic.exists(s) Then
                        n = n + 1
                        For c = 1 To UBound(k, 2)
                            kk(n, c) = k(i, c)
                        dic.Item(s) = n
                        c = dic.Item(s)
                        kk(c, 3) = kk(c, 3) & ", " & k(i, 3) 'update col c
                        kk(c, 4) = kk(c, 4) & ", " & k(i, 4) 'update col d
                    End If
                End If
            If n Then
                .Range("a2").Resize(n, UBound(kk, 2)).Value = kk
            End If
        End With
    End Sub

    Open in new window


    Author Closing Comment

    Awesome.  Works perfectly.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now