Combining multiple rows into a single row...Tricky!

Gruyere

New Member
Hello Forum!

I have a perplexing problem that is making my novice Excel brain hurt really bad. I have a HUGE spreadsheet (1,000,000 rows) that contains a series of reports. The first row of the report contains all of the report identification numbers. The next 20-50 rows contains the report details, all in column A. Then the report ends with a cell in column A containing "[report_end]" and then on the next row the next report begins.

I would like to compress the report details all into a single cell in column A so that there is only one row for each report, sort of like this:

AA | BB | CC | DD
xyz
123
[end_report]
AA | BB | CC | DD
xyz
123
[end_report]

becomes:

AA xyz 123 [end_report] | BB | CC | DD
AA xyz 123 [end_report] | BB | CC | DD

Any ideas for a novice? Thanks!

Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Any ideas for a novice? Thanks!

Maybe a macro to do the trick. Mind you, I often write 25 lines where 5 will due, but this worked for me with your sample data. Test it on a backup before using on a live sheet as it will delete rows.

Code:
``````Sub combineit()

Dim i As Long, j As Long, lr As Long
Dim TopOfRange As Long
Dim c1 As Range, c2 As Range, rng As Range
Dim cel As Variant
Dim TempStr As String

lr = Range("A65536").End(xlUp).Row
For i = lr To 1 Step -1
If Cells(i, "A") = "[end_report]" Then
For j = i - 1 To 1 Step -1
If j = 1 Then
TopOfRange = j
Exit For
ElseIf Cells(j, "A") = "[end_report]" Then
TopOfRange = j + 1
Exit For
End If
Next j
Set c1 = Cells(TopOfRange, "A")
Set c2 = Cells(i, "A")
Set rng = Range(c1, c2)
TempStr = ""
For Each cel In rng
TempStr = TempStr & cel.Value & " "
Next cel
Cells(TopOfRange, "A") = Trim(TempStr)
End If
If i <> TopOfRange Then
Rows(i).Delete
End If
Next i``````

Hello Gruyere,

I have a question: Are the number of rows we need to concatenate fixed (or always four rows)?
If yes, we can restructure the data but will require multiple steps as shown below:

 Step1 Step2 Step3 Step4 Raw data using Text-to-Columns result Concatenate Secondary criterias Helper1 Helper2 Helper3 1 2 3 4 Final result AA BB CC DD BB | CC | DD 1 1 1 1 AA xyz 123 BB | CC | DD =L3&" "&M3&" "&N3&" "&O3 xyz 2 1 2 2 AA xyz 123 BB | CC | DD AA xyz 123 BB | CC | DD 123 3 1 3 [end_report] 4 1 4 AA BB CC DD BB | CC | DD 5 2 1 xyz 6 2 2 123 7 2 3 [end_report] 8 2 4

<colgroup><col span="4"><col><col><col span="3"><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

And formulas are the following:

 Step1 Step2 Step3 Step4 Raw data using Text-to-Columns result Concatenate Secondary criterias Helper1 Helper2 Helper3 1 2 3 4 Final result AA BB CC DD =IF(AND(A3<>"",B3<>""),B3&"|"&C3&"|"&D3,"") 1 =IF(MOD(\$G3,4)=0,G3/4,INT(G3/4)+1) =IF(H3<>H2,1,I2+1) =IF(K2>=MAX(\$H\$3:\$H\$10),"",K2+1) =OFFSET(\$A\$2,MATCH(\$K3,\$H\$3:\$H\$10,0)+MATCH(L\$2,\$I\$3:\$I\$10,0)-1,0) =OFFSET(\$A\$2,MATCH(\$K3,\$H\$3:\$H\$10,0)+MATCH(M\$2,\$I\$3:\$I\$10,0)-1,0) =OFFSET(\$A\$2,MATCH(\$K3,\$H\$3:\$H\$10,0)+MATCH(N\$2,\$I\$3:\$I\$10,0)-1,0) =OFFSET(\$F\$2,MATCH(\$K3,\$H\$3:\$H\$10,0),0) =L3&" "&M3&" "&N3&" "&O3 xyz =IF(AND(A4<>"",B4<>""),B4&"|"&C4&"|"&D4,"") =G3+1 =IF(MOD(\$G4,4)=0,G4/4,INT(G4/4)+1) =IF(H4<>H3,1,I3+1) =IF(K3>=MAX(\$H\$3:\$H\$10),"",K3+1) =OFFSET(\$A\$2,MATCH(\$K4,\$H\$3:\$H\$10,0)+MATCH(L\$2,\$I\$3:\$I\$10,0)-1,0) =OFFSET(\$A\$2,MATCH(\$K4,\$H\$3:\$H\$10,0)+MATCH(M\$2,\$I\$3:\$I\$10,0)-1,0) =OFFSET(\$A\$2,MATCH(\$K4,\$H\$3:\$H\$10,0)+MATCH(N\$2,\$I\$3:\$I\$10,0)-1,0) =OFFSET(\$F\$2,MATCH(\$K4,\$H\$3:\$H\$10,0),0) =L4&" "&M4&" "&N4&" "&O4 123 =IF(AND(A5<>"",B5<>""),B5&"|"&C5&"|"&D5,"") =G4+1 =IF(MOD(\$G5,4)=0,G5/4,INT(G5/4)+1) =IF(H5<>H4,1,I4+1) [end_report] =IF(AND(A6<>"",B6<>""),B6&"|"&C6&"|"&D6,"") =G5+1 =IF(MOD(\$G6,4)=0,G6/4,INT(G6/4)+1) =IF(H6<>H5,1,I5+1) AA BB CC DD =IF(AND(A7<>"",B7<>""),B7&"|"&C7&"|"&D7,"") =G6+1 =IF(MOD(\$G7,4)=0,G7/4,INT(G7/4)+1) =IF(H7<>H6,1,I6+1) xyz =IF(AND(A8<>"",B8<>""),B8&"|"&C8&"|"&D8,"") =G7+1 =IF(MOD(\$G8,4)=0,G8/4,INT(G8/4)+1) =IF(H8<>H7,1,I7+1) 123 =IF(AND(A9<>"",B9<>""),B9&"|"&C9&"|"&D9,"") =G8+1 =IF(MOD(\$G9,4)=0,G9/4,INT(G9/4)+1) =IF(H9<>H8,1,I8+1) [end_report] =IF(AND(A10<>"",B10<>""),B10&"|"&C10&"|"&D10,"") =G9+1 =IF(MOD(\$G10,4)=0,G10/4,INT(G10/4)+1) =IF(H10<>H9,1,I9+1)

<colgroup><col span="4"><col><col><col span="3"><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

<colgroup><col span="7"><col><col><col span="5"><col><col><col></colgroup><tbody></tbody>
Step1: Use text to column under data tab. Click on delimited then type the text "|" as a delimiter under other.
Step2: Concatenate the last three criteria ("BB", "CC" and "DD"). Formula works assuming that there are four criteria.
Step4: Add the a table for result.

As an afterthought, I realized you said it was over a million rows, so my last row detection won't work in the code I posted.

Replace this
Code:
``lr = Range("A65536").End(xlUp).Row``

with this
Code:
``lr = Range("A" & Rows.count).End(xlUp).Row``

and it should work just fine.

As an afterthought, I realized you said it was over a million rows, so my last row detection won't work in the code I posted.

Replace this
Code:
``lr = Range("A65536").End(xlUp).Row``

with this
Code:
``lr = Range("A" & Rows.count).End(xlUp).Row``

and it should work just fine.

First, thanks for your help on this...I have a feeling I'm getting close.

One issue. When I run the macro, it deletes all of the cells and leaves me with a blank worksheet. Any idea what's going on? Here's a copy of the macro, which I'm running as a general VBA macro (no command button):
Sub combineit()
Dim i As Long, j As Long, lr As Long
Dim TopOfRange As Long
Dim c1 As Range, c2 As Range, rng As Range
Dim cel As Variant
Dim TempStr As String
lr = Range("A" & Rows.Count).End(xlUp).Row
For i = lr To 1 Step -1
If Cells(i, "A") = "[end_report]" Then
For j = i - 1 To 1 Step -1
If j = 1 Then
TopOfRange = j
Exit For
ElseIf Cells(j, "A") = "[end_report]" Then
TopOfRange = j + 1
Exit For
End If
Next j
Set c1 = Cells(TopOfRange, "A")
Set c2 = Cells(i, "A")
Set rng = Range(c1, c2)
TempStr = ""
For Each cel In rng
TempStr = TempStr & cel.Value & " "
Next cel
Cells(TopOfRange, "A") = Trim(TempStr)
End If
If i <> TopOfRange Then
Rows(i).Delete
End If
Next i
End Sub

Hello Gruyere,

I have a question: Are the number of rows we need to concatenate fixed (or always four rows)?
If yes, we can restructure the data but will require multiple steps as shown below:

Unfortunately not. They range from 20 to 60 rows. Totally variable.

Replies
1
Views
213
Replies
0
Views
335
Replies
2
Views
303
Replies
1
Views
122
Replies
4
Views
446

1,220,965
Messages
6,157,123
Members
451,399
Latest member
alchavar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back