Combine rows based on previous column duplicate data

[jessiehayden]

Hi,

I’m a bit of an Excel noob, so I hope I’m able to express myself clearly. Using Office Professional Plus 2019 on a Windows 10 computer.

I'm working with a large dataset of TV show transcripts. Column A ('speaker') has the names of the characters, and column B ('line') has the lines they speak. Column A 'speaker' has duplicate values, while column B 'line' has unique values.

I want to merge rows that have duplicate values in column A 'speaker', with the values in column B 'line' being combined in the order they appear with a space (' ') as the delimiter, but only if they are consecutive in column A.

To show what I mean, I want to turn this:

into this:

As you can see, you have the duplicate values (e.g ‘Leslie Knope’) appearing again in column A if they follow another name, so they’re not all merged together.

Is there any way to do it automatically? Thanks so much!

 

[Cubist]

Try this on a copy.

Sub ConcatenateLines()

    Dim lastRow As Long
    Dim i As Long
    Dim currentSpeaker As String
    Dim concatenatedLine As String
    Dim firstInstanceRow As Long
    
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    currentSpeaker = Cells(2, 1).Value
    concatenatedLine = Cells(2, 2).Value
    firstInstanceRow = 2
    
    For i = 3 To lastRow

        If Cells(i, 1).Value = currentSpeaker Then
            concatenatedLine = concatenatedLine & " " & Cells(i, 2).Value
            Cells(i, 1).ClearContents
            Cells(i, 2).ClearContents
        Else
            Cells(firstInstanceRow, 2).Value = concatenatedLine
            currentSpeaker = Cells(i, 1).Value
            concatenatedLine = Cells(i, 2).Value
            firstInstanceRow = i
        End If
    Next i

    If curentSpeaker = Cells(firstInstanceRow, 1).Value Then
        concatenatedLine = concatenatedLine & " " & Cells(firstInstanceRow - 1, 2).Value
        Cells(firstInstanceRow - 1, 2).Value = concatenatedLine
    Else
        Cells(firstInstanceRow, 2).Value = concatenatedLine
    End If

End Sub

 

[jessiehayden]

Try this on a copy.

Sub ConcatenateLines()

    Dim lastRow As Long
    Dim i As Long
    Dim currentSpeaker As String
    Dim concatenatedLine As String
    Dim firstInstanceRow As Long
   
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    currentSpeaker = Cells(2, 1).Value
    concatenatedLine = Cells(2, 2).Value
    firstInstanceRow = 2
   
    For i = 3 To lastRow

        If Cells(i, 1).Value = currentSpeaker Then
            concatenatedLine = concatenatedLine & " " & Cells(i, 2).Value
            Cells(i, 1).ClearContents
            Cells(i, 2).ClearContents
        Else
            Cells(firstInstanceRow, 2).Value = concatenatedLine
            currentSpeaker = Cells(i, 1).Value
            concatenatedLine = Cells(i, 2).Value
            firstInstanceRow = i
        End If
    Next i

    If curentSpeaker = Cells(firstInstanceRow, 1).Value Then
        concatenatedLine = concatenatedLine & " " & Cells(firstInstanceRow - 1, 2).Value
        Cells(firstInstanceRow - 1, 2).Value = concatenatedLine
    Else
        Cells(firstInstanceRow, 2).Value = concatenatedLine
    End If

End Sub

Thank you so much, it worked perfectly!