Combine data for duplicate rows

Posted on 2011-04-23
Last Modified: 2012-05-11
I have a Google Sheet

In this sheet, I want to combine the data in Col E and Col G for all the duplicates.

I want only one entry with Col E data combined and Col G data combined. The data from different cells should be separated by a semi colon and a space.

Can this be done?
Question by:rtod2
    1 Comment
    LVL 30

    Accepted Solution

    Yes with the help of Excel Sheet. What I would recommend you is to copy the data from the Google Sheet to a normal Excel File and then run a macro to combine the data and then paste it back to the Google Doc.

    Here is a sample. I just copied the data from the Google Sheet and pasted it in an Excel Sheet. When you select sheet 1 and run the macro you will get the desired data.

    Hope this is what you wanted? Let me know if you get any errors.


    Code Used

    '~~> Please ensure that the data is osrted for this to work
    Sub CombineData()
        Dim i As Long, lastRow As Long
        Dim Rng As Range
        Dim str1 As String, str2 As String
        On Error GoTo Whoa
        Application.ScreenUpdating = False
        lastRow = Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
        Set Rng = Sheets("Sheet1").Range("B3:B" & lastRow)
        For i = lastRow To 3 Step -1
            str1 = Range("E" & i).Value
            str2 = Range("G" & i).Value
            If Range("B" & i).Value = Range("B" & i - 1).Value Then
                If Len(Trim(Range("E" & i - 1).Value)) <> 0 Then _
                Range("E" & i).Value = Range("E" & i - 1).Value & "; " & str1
                If Len(Trim(Range("G" & i - 1).Value)) <> 0 Then _
                Range("G" & i).Value = Range("G" & i - 1).Value & "; " & str2
                Rows(i - 1).Delete shift:=xlUp
            End If
        MsgBox "Data combined successfully"
        Application.ScreenUpdating = True
        Exit Sub
        MsgBox Err.Description
        Resume LetsContinue
    End Sub

    Open in new window


    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    733 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

    18 Experts available now in Live!

    Get 1:1 Help Now