Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

Combine data for duplicate rows

I have a Google Sheet https://spreadsheets.google.com/ccc?key=0ApGg6c9aeywQdExrZjBzUnRER25KSUQxRTBJTDRObXc&hl=en&authkey=CI3z8Y8D#gid=0

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?
1 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now