Concatenate Multiple Rows until Non-Blank in Adjacent Column

[bencookman]

Hello

I would like a macro that works through a long worksheet, set up as follows:

Column A: product code (3-digit code)
Column B: notes about product (free text - can span multiple rows due to character limits)

Column A is blank in the rows where the data in column B has spilled onto multiple rows.

I would like a macro that will loop the worksheet and and concatenate the contents of column B into a new column C, so that all the notes for a given product are shown in a single row. The icing on the cake would be to delete the now redundant blank rows between product codes.

I'm struggling with the "keep concatenating the rows in column B until you find a non-blank entry in column A" piece of it. All help appreciated.

 

[jasonb75]

If this is a one off then you really don't need a macro for the task.

Assuming headers in row 1, data starting in row 2, enter this formula into C2 and fill down to the end of your data.

=IF(A3<>"",B2,B2&C3)

Next copy column C and pastespecial - values.

Finally, filter column A to show only blanks and delete the visible rows.

 

[bencookman]

If this is a one off then you really don't need a macro for the task.

Assuming headers in row 1, data starting in row 2, enter this formula into C2 and fill down to the end of your data.

=IF(A3<>"",B2,B2&C3)

Next copy column C and pastespecial - values.

Finally, filter column A to show only blanks and delete the visible rows.

Jason - many thanks - works a treat, and would never have thought that this could be so simple!