Excel Macro to Scan document and combine rowsSolved/Closed

Question

Hello,

I'm trying to write a Macro that will combine text from several rows in a given column (column B in the example below) into one cell as long as the cell in column A remains NULL.  For example:

	A	B
1	ID	TEXT
2	1234	Example Text1
3		Example Text2
4		Example Text3
5	2345	Example Text4
6		Example Text5
7		Example Text6

I found this Macro online which will combine the text into a single cell for me, but it requires that I manually select the range of cells to be combined.  As I will have to combine approximately 15000 rows of data I don't really want to have to do it manually :)

Sub JoinText()

myRow = Selection.Rows.Count

For i = 1 To myRow - 1
ActiveCell = ActiveCell.Offset(0, 0) & (Chr(13) & Chr(10)) & ActiveCell.Offset(i, 0)
ActiveCell.Offset(i, 0) = ""
Next i
End Sub

I'm hoping that someone will be able to help me automate this.  What I'd like it to have the macro start in cell A2 and then scan column A and as soon as it finds a value populated in column A, to combine the text in column B for each row until a cell in column A is populated again.  It should check the entire way down the spreadsheet until all cells are null.

I'd appreciate any help that cnyone can provide!

venkat1926 · Accepted Answer

your data is from A1 to B7 with row1 having column headings.
the macro will work if your have more rows provided the sheet starts from A1

try this macro 

Sub test()
Dim rng As Range, x() As String, c As Range
Dim j As Integer, k As Integer, lastrow As Integer, y As String
 Worksheets("sheet1").Activate
 Columns("c:c").Columns.Delete
lastrow = Range("B1").End(xlDown).Row
Set rng = Range("a2")
'msgbox rng.Address
line2:
'msgbox rng.End(xlDown).Row
If rng.End(xlDown).Row > lastrow Then
j = rng.Offset(0, 1).End(xlDown).Row - rng.Row + 1
Else
j = rng.End(xlDown).Row - rng.Row
End If
'msgbox j
ReDim x(1 To j)
y = ""
For k = 1 To j
x(k) = rng.Offset(k - 1, 1)
y = y & " " & x(k)
'msgbox x(k)
'msgbox y
Next
rng.Offset(0, 2) = y
Set rng = rng.End(xlDown)
'msgbox rng.Address
If rng.Offset(0, 1) = "" Then
GoTo line1
Else
GoTo line2
End If
line1:
Columns("a:c").AutoFit
End Sub

after running the macro your sheet will be like this 

ID	TEXT	
1234	EXAMPLE TEXT1	 EXAMPLE TEXT1 EXAMPLE TEXT2 EXAMPLE TEXT3
	EXAMPLE TEXT2	
	EXAMPLE TEXT3	
2345	EXAMPLE TEXT4	 EXAMPLE TEXT4 EXAMPLE TEXT5 EXAMPLE TEXT6
	EXAMPLE TEXT5	
	EXAMPLE TEXT6

venkat1926 · Answer

Why do you want a code 

for e.g a2 is 1234 and b2 is example text1

then in c2 type this formula 

=IF(OR(ISBLANK(A2),ISBLANK(B2)),"",A2&B2)

copy c2 down the 5000 rows. to quickly copy take the cursor to the bottom
right the cursor will turn to + then click this it will be copied down.
now check

Dbono · Answer

Thanks for the info, but I may not have been clear about what I was trying to do...

I'm importing legacy data from an out of date defect tracking system into a new system (JIRA).  All of the fields from the defects have a column in the CSV file (defect id is column A, and steps to reproduce is column B, etc...).  When the reports were generated from the old system the steps to reproduce were imported into successive rows for each step while the rest of the data for each defect is in just one row.  So there can be multiple rows in column B (and it varies for each defect) for each defect ID in column A.

For example:

Cell A2 contains defect ID 1234.  
Cells B2:B5 each contain one sentence.
Cell A6 contains defect ID 1235.
Cells B6:B8 each contain one sentence.

I need a macro which will start in cell A2, determine that it is not null and then combine the text in each successive row in column B until a cell in column A is populated again.  So in the above example cells B2:B5 would be combined into cell B2 and cells B6:B8 would be combined into cell B6.

Basically I know that the macro above will combine the data correctly, I just need to figure out how to define the range based on if column A remains null.  

Sorry, it's hard to explain...  Thanks again for any and all help you can provide!

DBono · Answer

Thanks Venkat1926!  It works, might require some slight tweaking but this saved me a lot of time and helps a ton!

Excel Macro to Scan document and combine rows

4 responses

Subscribe To Our Newsletter!