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