Automatically concatenate text in cells containing specific text

[cmaunder]

Hi all,

I have what I hope is a simple problem to resolve.

I have a spreadsheet which contains rows called "Sample ID" and "Asbestos Content" (see attached example).

Where a cell in the "Sample ID" column contains the words "Visually Consistent with...", I need to
a) add parentheses around the Asbestos Content cell beside it in the same row,
b) concatenate the text in the "Sample ID" and "Asbestos Content" cells in the same row into the "Asbestos Content" cell, with a hard return separating the concatenated text, and
c) Insert the text string "Not Sampled" in the "Sample ID" cell from the row being altered.

This isn't too hard to do manually using the concatenate formula, but not being able to automate the process over hundreds of rows in some cases is driving me nuts.

I've attached both a large dataset in need of this automation, and an example spreadsheet with "before and after" examples. The latter contains two pairs of two rows highlighted in blue and in purple. In each pair of rows the top is what I have currently and the bottom is what I want these cells to all look like.

Any help is greatly appreciated.