Concat rows -

[nmpb]

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;000034001589
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

 

[Joel]

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]

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.

 

[nmpb]

could you also exclude the duplicates in column B.

Thank you

 

[GB]

If you change the line in the second if statement that reads:
Range("B" & RowCount) = Data
To
Range("C" & RowCount) = Data

Then your "answer" will be stored in Column C instead of column B.

 

[nmpb]

Thank you - now I just need to exclude the duplicates

 

[GB]

This could be a tough question to answer without more information from you.

If I assume that you only want to capture the first instance of the data in
column B the first time (instead of the last time) that it is used, then
within the first if statement, just after the else statement, change this to
perform a search of the data from row 1 to the row before rowcount by using
something like a selection.find operation to find the value of the current
"Range". If the value is not found then run the

Data = Data & ";" ...etc.

if the data is found then do nothing.

So the following will combine the rows into a single cell and maintain your
original data integrity, placing the result (data not previously found in
column B) in the row that contains data in column A.

Option Explicit

Public Sub testCombineRows()

Dim RowCount As Long
Dim Data As String
Dim FirstRowData As Long

RowCount = 1
Data = ""
FirstRowData = RowCount
Do While Range("B" & RowCount) <> ""
If RowCount > 1 Then
Range("B1:B" & RowCount - 1).Select
Else
'At the first row, but still want to add it.
End If

If RowCount = 1 Then
Data = Range("B" & RowCount)
ElseIf Selection.Find(Range("B" & RowCount)) Is Nothing And RowCount >
1 And Data = "" Then
Data = Range("B" & RowCount)
ElseIf Selection.Find(Range("B" & RowCount)) Is Nothing Then
Data = Data & ";" & Range("B" & RowCount)
Else
'The data has been found before
End If

If Range("A" & (RowCount + 1)) <> "" Or Range("B" & (RowCount + 1)) = ""
Then
Range("C" & FirstRowData) = Data
Data = ""
RowCount = RowCount + 1
FirstRowData = RowCount
Else
RowCount = RowCount + 1
'Rows(RowCount).Delete
End If
Loop


Worked for me, hope it does what you wanted. (I commented out the
Rows(RowCount).Delete line so that no data would not be deleted.
End Sub

 

[GB]

See thread above that asked the question about excluding duplicates.

 

[nmpb]

Wow! GB - THANKS
I did not see this reply before I posted mine. I will test and let you know
the outcome.

 

[Joel]

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]

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]

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

 

[Joel]

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

 

[GB]

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 are:
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.