So, nmpb, to restate your original question/request, you wanted to
concatenate all data in column B up to the next instance of data in Column A
or the end of Column B placing the result in Column C (and subsequently the
length of the concatenated text in Column D) at the row that meets either of
the above 2 conditions given the following:

1. All data is sequential in Columns A and B and therefore a duplicate
number would only appear immediately after the first instance, and not
elsewhere within the data. (FYI, the original dataset did not present itself
as sequential, row 6 is less than row 7; however, row 7 is greater than row
8. Therefore if a duplicate did exist between row 6 and row 8 for example,
the duplicate would not be excluded by the above code.)
2. Upon concatenation, the only results needed a
2.a The concatenated text in Column C,
2.b The length of the text in Column C to be placed in Column D,
2.c The data that is in Column B is only needed for the last row before the
end of the data or the next instance of data in Column A, and
2.d The data in Column A is only needed if there is only one instance of
data in Column B associated with the data in Column A.
3. The total number of rows is to be reduced by the efforts of the
concatenation.
4. Data in Column A is used to indicate a one or many relationship between
Column A and Column B data based on the existence or absence of data in
Column A, respectively.

"Joel" wrote:

I only saw the 1st request on your last posting, Missed items 2 & 3. Here
are all the changes

Sub CombineRows()

Dim NewData As String

RowCount = 1
Data = ""
Do While Range("B" & RowCount) < ""
NewData = Format(Range("B" & RowCount).Text, "@")
If Data = "" Then
Data = NewData
Else
If NewData < LastData Then
Data = Data & ";" & NewData
End If
End If
LastData = NewData

If Range("A" & (RowCount + 1)) < "" Or _
Range("B" & (RowCount + 1)) = "" Then

Range("C" & RowCount).NumberFormat = "@"
Range("C" & RowCount).Text = Data
Range("D" & RowCount) = Len(Data)
Data = ""
RowCount = RowCount + 1
Else
Rows(RowCount).Delete
End If-
Loop
End Sub

"Joel" wrote:

Are the cells shown formated as Text. I made changes below to force
everything the string data and store the data as Text in the worksheet. You
are concatenating the number together with a semicolon which will
automatically convert these numbers as text.

Sub CombineRows()

Dim NewData as String

RowCount = 1
Data = ""
Do While Range("B" & RowCount) < ""
NewData = Format(Range("B" & RowCount).Text,"@")
If Data = "" Then
Data = NewData
Else
if NewData < LastData then
Data = Data & ";" & NewData
End if
End If
LastData = NewData

If Range("A" & (RowCount + 1)) < "" Then
Range("C" & RowCount).numberformat = "@"
Range("C" & RowCount).Text = Data
Data = ""
RowCount = RowCount + 1
Else
Rows(RowCount).Delete
End If
Loop
End Sub


"nmpb" wrote:

This does do want I want, so THANK YOU. I have done some testing and the
prg needs a small adjustment:
1) where there is only only entry, ie column A and B match - the output in C
is truncating the leading zeros.
Output:
A B C
1 000034028105 000034028105 34028105
2 000034061517 000034061517 34061517
3 000034008980 000034008980 34008980

2) could you add the len of the output into column D please. I tried using
len, but for the ones that get joined I am not getting the correct answer, it
just get the answer 8.
3) It seems to miss the last entry. I have worked around this by adding a
1 to columns A and B.

"Joel" wrote:

This code assumes the data in column B is in ascending order.

Sub CombineRows()

RowCount = 1
Data = ""
Do While Range("B" & RowCount) < ""
NewData = Range("B" & RowCount)
If Data = "" Then
Data = NewData
Else
if NewData < LastData then
Data = Data & ";" & NewData
End if
End If
LastData = NewData

If Range("A" & (RowCount + 1)) < "" Then
Range("C" & RowCount) = Data
Data = ""
RowCount = RowCount + 1
Else
Rows(RowCount).Delete
End If
Loop
End Sub

"nmpb" wrote:

Thank you for such a quick response. It works.
Can you change it so that the result appears in column C please so it does
not overwrite the data in column B.


"Joel" wrote:

Sub CombineRows()

RowCount = 1
Data = ""
Do While Range("B" & RowCount) < ""
If Data = "" Then
Data = Range("B" & RowCount)
Else
Data = Data & ";" & Range("B" & RowCount)
End If

If Range("A" & (RowCount + 1)) < "" Then
Range("B" & RowCount) = Data
Data = ""
RowCount = RowCount + 1
Else
Rows(RowCount).Delete
End If
Loop
End Sub


"nmpb" wrote:

Hi
I need a program to be able to concate all the rows in B until the cell in
column A is
not blank. would also want a separator between them.
ie row C1 = 000034001570;000034001571;000034001582;00003400158 9
C6 = 000034032303

A B
1 000034001570 000034001570
2 000034001571
3 000034001582
4 000034001589
5 000034001589
6 000034032303 000034032303
7 000034066598 000034066598
8 000034017214 000034017214
9 000034017215
10 000034019302
11 000034019303

The issue is that I have a sheet of over 40,000 rows, also I have noticed
that I have duplicates in the column B, which will have to be excluded.
Its been a while since I've done any programming so any help appreciated