Excel VBA: Deleting duplicate rows and merging cells with unique data

Question

I have a file that contains contact info. There's 44 columns and 680 rows. Each row contains one person's data and each column is a different piece of data as well. The problem is that there are multiple rows for most of the people and many times each row contains redundant info as well as unique.

Note:

There's no pattern to how many rows for each person, some can have 3, some only 1
Sometimes there are no unique values in one of the rows
Sometimes a cell might just be blank

My Question:

How can I merge the rows so that I have one row per person without losing the unique data from each row?

What I have:

What I need:

P.S. In the "what I need" image I put the merged unique data into the same cell but separated by a comma. To be honest it would be nice if I can automatically have it that a new column is created for the unique data (so for example if there's a new cell # it adds a column and puts the unique cell value in the row that will now be the only row for that person.

If that's too hard it's fine, I can just do text to column.

Thanks!

Vasily · Accepted Answer · 2018-10-23 23:14:57Z

you can use something like this:

Sub test()
    Dim Dic As Object: Set Dic = CreateObject("Scripting.Dictionary")
    Dic.comparemode = vbTextCompare

    Dim rng As Range: Set rng = Range([A1], Cells(Rows.Count, "A").End(xlUp))
    Dim cl As Range, sPhone$, sCell$, sEmail$, sAddress$

    For Each cl In rng

        sPhone = Cells(cl.Row, "B").Value2
        sCell = Cells(cl.Row, "C").Value2
        sEmail = Cells(cl.Row, "D").Value2
        sAddress = Cells(cl.Row, "E").Value2

        If Not Dic.exists(cl.Value2) Then
            Dic.Add cl.Value2, sPhone & "|" & sCell & "|" & sEmail & "|" & sAddress
        Else
            If Not (Split(Dic(cl.Value2), "|")(0) Like "*" & sPhone & "*") And sPhone <> "" Then
                Dic(cl.Value2) = sPhone & ", " & _
                                 Split(Dic(cl.Value2), "|")(0) & "|" & _
                                 Split(Dic(cl.Value2), "|")(1) & "|" & _
                                 Split(Dic(cl.Value2), "|")(2) & "|" & _
                                 Split(Dic(cl.Value2), "|")(3)
            End If
            If Not Split(Dic(cl.Value2), "|")(1) Like "*" & sCell & "*" And sCell <> "" Then
                Dic(cl.Value2) = Split(Dic(cl.Value2), "|")(0) & "|" & _
                                 sCell & ", " & _
                                 Split(Dic(cl.Value2), "|")(1) & "|" & _
                                 Split(Dic(cl.Value2), "|")(2) & "|" & _
                                 Split(Dic(cl.Value2), "|")(3)

            End If
            If Not Split(Dic(cl.Value2), "|")(2) Like "*" & sEmail & "*" And sEmail <> "" Then
                Dic(cl.Value2) = Split(Dic(cl.Value2), "|")(0) & "|" & _
                                 Split(Dic(cl.Value2), "|")(1) & "|" & _
                                 sEmail & "," & _
                                 Split(Dic(cl.Value2), "|")(2) & "|" & _
                                 Split(Dic(cl.Value2), "|")(3)

            End If
            If Not Split(Dic(cl.Value2), "|")(3) Like "*" & sAddress & "*" And sAddress <> "" Then
                Dic(cl.Value2) = Split(Dic(cl.Value2), "|")(0) & "|" & _
                                 Split(Dic(cl.Value2), "|")(1) & "|" & _
                                 Split(Dic(cl.Value2), "|")(2) & "|" & _
                                 sAddress & "," & _
                                 Split(Dic(cl.Value2), "|")(3)

            End If
        End If
    Next cl

    Dim key, i&, ws As Worksheet
    Set ws = Worksheets.Add: ws.Name = "Result " & Replace(Now, ":", "-")
    With ws
        i = 1
        For Each key In Dic
            .Cells(i, "A").Value2 = key
            .Cells(i, "B").Value2 = Split(Dic(key), "|")(0)
            .Cells(i, "C").Value2 = Split(Dic(key), "|")(1)
            .Cells(i, "D").Value2 = Split(Dic(key), "|")(2)
            .Cells(i, "E").Value2 = Split(Dic(key), "|")(3)
            i = i + 1
        Next key
        ws.Columns("A:E").AutoFit
    End With
End Sub

test:

First off, thanks so much for your help! How do I customize this code to a specific file (One that has different columns than the one I gave in the example)

Collectives™ on Stack Overflow

Excel VBA: Deleting duplicate rows and merging cells with unique data

My Question:

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

My Question:

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related