Concatenate Data

[DetroitDavid]

I’m trying to combine some data and it’s becoming very tedious. When I try to record a macro, it’s using absolute cell references and it’s not working.

My spreadsheet has 2 columns. In column A are terms or phrases, in column B are things related to that term or phrase. However, the data is laid out so that when column B has more than one related bit of information, it shows in multiple rows. Sometimes the rows are blank. I’d like to combine the related data in column B that goes with a term in column A.

While combining it (using concatenate)? Then to separate the information I’d like to add a period and space at the end of each bit of information, then insert a line-break (Alt+Enter).

New terms in column A are always followed be blank cells under them if column B has more than 1 cell of information.

A1 = Term 1 B1 = related information for Term 1
A2 = Term 2 B2 = related information for Term 2
A3 = Blank (part of Term 2) B3 = additional related information for Term 2
A4 = Blank (part of Term 2) B4 = additional related information for Term 2
A5 = Term 3 B5 = related information for Term 3
A6 = Blank (part of Term 3) B6 = blank but part of Term 3
A7 = Blank (part of Term 3) B7 = additional related information for Term 3

So, for every Term in column A, it has at least 1 related term in column B of the same row (in which case I don’t need anything done). But when there are more related bits of information, I’d like to combine them and delete the uncombined used information.

Example:
A1 = INCOME APPROACH?
B1 = Used to estimate value on income property
B2 = VALUE=NET OPERATING INCOME/CAPITALIZATION RATE
B3 =
B4 = Net operating income(NOI)
B5 = -Gross rent-Operating Expenses

B6 = Cash Flow=NOI-Debt Service
B7 =
B8 = Capitalization Rate (CAP) Rate of return

Would become:

A1 = INCOME APPROACH?
B1 = Used to estimate value on income property.
VALUE=NET OPERATING INCOME/CAPITALIZATION RATE.
Net operating income(NOI).-Gross rent-
Operating Expenses. Cash Flow=NOI-Debt Service.
Capitalization Rate(CAP) Rate of return.

With all the information in column B in a single cell with the lines separated by a soft break.

I need to run this so it would cover several thousand rows.

Can anyone help?

TIA - DD