# 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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### 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.
Step3: Add helper columns.
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
35
Views
489
Replies
1
Views
147
Replies
1
Views
43
Replies
1
Views
939
Replies
0
Views
109

1,171,533
Messages
5,876,044
Members
433,170
Latest member
Gott

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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