Combining rows based on previous column duplicate data

Hi,

I'm a bit of an Excel noob, so I hope I'm able to express myself clearly.

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.

To show what I mean, I've attached an Excel file with 'before' and 'after' sheets.

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!

Welcome to the forum.

Proposed solution.

Formula:

Please Login or Register  to view this content.