Excel Macro to Scan document and combine rows
Solved/Closed
dbono
-
Jul 8, 2009 at 11:54 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Feb 2, 2010 at 03:58 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Feb 2, 2010 at 03:58 PM
Related:
- Excel Macro to Scan document and combine rows
- Combine notifications viber - Guide
- Kaspersky online scan - Guide
- Spell number in excel without macro - Guide
- Excel marksheet - Guide
- Excel free download - Download - Spreadsheets
4 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 9, 2009 at 06:02 AM
Jul 9, 2009 at 06:02 AM
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
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
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
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 8, 2009 at 09:22 PM
Jul 8, 2009 at 09:22 PM
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
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!
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!
Feb 2, 2010 at 03:35 PM
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
Feb 2, 2010 at 03:58 PM