Concatenate Multiple Rows until Non-Blank in Adjacent Column

bencookman

New Member
Joined
Mar 15, 2006
Messages
21
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.
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,629
Office Version
2019
Platform
Windows
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

New Member
Joined
Mar 15, 2006
Messages
21
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!
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,179
Messages
5,509,643
Members
408,746
Latest member
Faker4442

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top