Concat rows -
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
|