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

[Gruyere]

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!

 

[rjwebgraphix]

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.

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]

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]

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

lr = Range("A65536").End(xlUp).Row

with this

lr = Range("A" & Rows.count).End(xlUp).Row

and it should work just fine.

 

[Gruyere]

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

lr = Range("A65536").End(xlUp).Row

with this

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]

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.