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

x
  • 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?
0
rtod2
Asked:
rtod2
1 Solution
 
SiddharthRoutCommented:
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.

Sid

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
    Next
    
    MsgBox "Data combined successfully"
LetsContinue:
    Application.ScreenUpdating = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

Open in new window

Ted.xlsm
0

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