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

Gruyere

New Member
Joined
Apr 9, 2013
Messages
3
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!
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

rjwebgraphix

Well-known Member
Joined
May 25, 2010
Messages
588
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
Joined
Nov 16, 2012
Messages
50
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:

Step1Step2Step3 Step4
Raw data using Text-to-Columns resultConcatenate Secondary criteriasHelper1Helper2Helper3 1234 Final result
AA BB CC DD BB | CC | DD1111AA xyz123 BB | CC | DD=L3&" "&M3&" "&N3&" "&O3
xyz2122AA xyz123 BB | CC | DDAA xyz 123 BB | CC | DD
123313
[end_report]414
AA BB CC DD BB | CC | DD521
xyz622
123723
[end_report]824

<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:

Step1Step2Step3 Step4
Raw data using Text-to-Columns resultConcatenate Secondary criteriasHelper1Helper2Helper3 1234 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
Joined
May 25, 2010
Messages
588
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
Joined
Apr 9, 2013
Messages
3
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
Joined
Apr 9, 2013
Messages
3
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,143
Messages
5,599,975
Members
414,354
Latest member
Flaxarn

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?

Disable AdBlock

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
Top