Excel Macro to Scan document and combine rows [Solved/Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
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!

4 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
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
3
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month


Name                     ID             Type            Date         Location
Cornus florida	         523-8        G	            1982	19-NW
					                19-SW
					                 25-NE
					                 25-SE
					                 32-SW

I would like to merge/combine the location column:

Cornus florida 523-82 G 1982 19-NW, 19-SW, 25-NE, 25-SE, 32-SW

Is there a macro or other way to create this in excel?

I am starting with 5 columns, and 5 rows, and now i want a single row.

A cold frosty beverage is up for the first one to solve this for me... :-)

Thanks,
George
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760 > George from Boston
The macro that was given as solution should resolve your issue too. You may need a minor tweak or so
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
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
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!
Thanks Venkat1926! It works, might require some slight tweaking but this saved me a lot of time and helps a ton!