Hi - first time poster here! any help greatly appreciated!
I am trying to use excel as a quick way to format a transcript and either concatenate or merge cells to aggregate comments by a speaker, please see below example of data and my first ideas at the logic of trying to resolve:
herei thought i could try and make a colum (colum C) so that if cell a2 is not blank then concatenate from C2 to another cell reference (but here I need a reference for the cell up to the next non blank cell.)
Ideally i would like the final data to look like this:
does anyone have any suggestions please? happy with formulas or i could try and run VBA but its been a few years - maybe i need a loop in VBA?
I am trying to use excel as a quick way to format a transcript and either concatenate or merge cells to aggregate comments by a speaker, please see below example of data and my first ideas at the logic of trying to resolve:
Column A | Column B | Column c = the output |
natalie | comment 1 | |
comment 2 | comment 1comment 2 | |
other | comment 3 | |
comment 4 | comment 3comment 4 | |
natalie | comment 5 | comment 5 |
other | comment 6 | comment 6 |
herei thought i could try and make a colum (colum C) so that if cell a2 is not blank then concatenate from C2 to another cell reference (but here I need a reference for the cell up to the next non blank cell.)
Ideally i would like the final data to look like this:
Column A | Column c (merged for each cell where speaker is natalie) |
natalie | comment 1comment 2 |
other | comment 3comment 4 |
natalie | comment 5 |
other | comment 6 |
does anyone have any suggestions please? happy with formulas or i could try and run VBA but its been a few years - maybe i need a loop in VBA?