How to concatenate using another column as a reference

[notaguru1]

Hi,

I have the following workseet as an example:

A	1	2345
	2
	3
	4
	5
A	6	789
	7
	8
	9
A	10	11
	11

<TBODY>
</TBODY>

Looking at column A, I want to concatenate values in column B starting in row 2 up until the next row where there is a value in column A.

So I imagine the formula would look something like this for the first value returned: =IF(A1="","",B2&B3&B4&B5) this would return 2345

But I dont know how to create this formula where it will be true for the entire sheet. My sheet has over 1000 rows of data.

 

[JoeMo]

I can't offer a formula, but here's a macro that will do what you want. Assumes a layout identical to your example.

Sub Notaguru()
Dim Ar As Range, c As Range, V As Variant
Application.ScreenUpdating = False
For Each Ar In Columns("A").SpecialCells(xlBlanks).Areas
          For Each c In Ar.Cells
                    V = V & c.Offset(0, 1).Value
          Next c
          Ar(1).Offset(-1, 2).Value = V
          V = ""
Next Ar
Application.ScreenUpdating = True
End Sub

 

[notaguru1]

this worked like a charm. thanks!

 

[JoeMo]

this worked like a charm. thanks!

You are welcome - thanks for the reply.