# 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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### rjwebgraphix

##### Well-known Member
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``````

#### saelageonzon

##### Board Regular
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.

#### rjwebgraphix

##### Well-known Member
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.

#### Gruyere

##### New Member
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

#### Gruyere

##### New Member
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
936
Replies
0
Views
244
Replies
8
Views
612
Replies
2
Views
743
Replies
1
Views
633

1,171,065
Messages
5,873,580
Members
432,985
Latest member
leahw

### 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.

### Which adblocker are you using?

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