Combine a Variable Number of Rows in Excel Based on Cell Contents

[zerocontinues]

Hey everyone,

Please help me pass the CPCU (a massive set of insurance tests). I'd like to combine rows of text in excel, to upload into a flashcard program. The format I start with looks something like this (each line would be its on its own row in its own cell):
[TABLE="width: 700"]
<tbody>[TR]
[TD][/TD]
[TD]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]293. Business intelligence refers to:
[/TD]
[/TR]
[TR]
[TD]A. The skills technologies applications
[/TD]
[/TR]
[TR]
[TD]and reinforce information integrity.
[/TD]
[/TR]
[TR]
[TD]B. A collection of information stored in discrete units for ease of retrieval manipulation
[/TD]
[/TR]
[TR]
[TD]combination or other computer processing.
[/TD]
[/TR]
[TR]
[TD]C. The percentage of policies in force that are renewed at the policy anniversary.
[/TD]
[/TR]
[TR]
[TD]D. A small segment of a total market.
[/TD]
[/TR]
[TR]
[TD]294. When determining the return on investment that new information technology would produce all of
[/TD]
[/TR]
[TR]
[TD]the following are costs insurers should consider EXCEPT:
[/TD]
[/TR]
[TR]
[TD]A. The value of the staff’s time for training.
[/TD]
[/TR]
[TR]
[TD]B. The costs of maintaining the technology.
[/TD]
[/TR]
[TR]
[TD]C. The salary and benefit costs eliminated through staff reductions.
[/TD]
[/TR]
[TR]
[TD]D. The value of time lost until staff become proficient using the new technology.
[/TD]
[/TR]
</tbody>[/TABLE]



I need to end up with something like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]293. Business intelligence refers to:
A. The skills technologies applications
and reinforce information integrity.
B. A collection of information stored in discrete units for ease of retrieval manipulation
combination or other computer processing.
C. The percentage of policies in force that are renewed at the policy anniversary.
D. A small segment of a total market.
[/TD]
[/TR]
[TR]
[TD]294. When determining the return on investment that new information technology would produce all of
the following are costs insurers should consider EXCEPT:
A. The value of the staffÂ’s time for training.
B. The costs of maintaining the technology.
C. The salary and benefit costs eliminated through staff reductions.
D. The value of time lost until staff become proficient using the new technology.
[/TD]
[/TR]
</tbody>[/TABLE]


The questions take up a varying number of rows. The only constant is that the next number in the sequence will signify a new question. There are thousands of questions I have so this would be a huge help!! Thank you![/TD]
[/TR]
</tbody>[/TABLE]

 

[Takae]

Hope this helps.

Sub test()
Dim i As Long, LR As Long, j As Long
Dim buf As String, x
LR = cells(Rows.count, 1).End(xlUp).Row
buf = Range("A2").Value
j = 2
For i = 3 To LR
    On Error Resume Next
    x = Left(cells(i, 1).Value, 1) * 1
    If x = Empty Then x = Left(cells(i, 1).Value, 1)
    On Error GoTo 0
    If VarType(x) = 8 Then
        buf = buf & vbCrLf & cells(i, 1).Value
        x = ""
    Else
        cells(j, 2).Value = buf
        j = j + 1
        buf = cells(i, 1).Value
        x = ""
    End If
Next
 cells(j, 2).Value = buf
End Sub

 

[mikerickson]

Perhaps

Sub test()
    Dim outRow As Long, arrOut() As String
    Dim oneCell As Range, inRange As Range
    
    With Range("A:A")
        Set inRange = Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
    End With
    
    ReDim arrOut(1 To inRange.Cells.Count, 1 To 1)

    For Each oneCell In inRange
        If 0 < Val(oneCell.Value) Then outRow = outRow + 1
        arrOut(outRow, 1) = arrOut(outRow, 1) & vbCr & oneCell.Value
    Next oneCell
    
    Range("c1").Resize(outRow, 1).Value = arrOut
End Sub

 

[zerocontinues]

Mike Ericson - I'm hoping you could make one small change. Is it possible to enter a carriage return after each cell that is input into the newly created cell?

Thanks!

 

[mikerickson]

Try

arrOut(outRow, 1) = arrOut(outRow, 1) & OneCell.Value & vbCr

 

[zerocontinues]

Try

arrOut(outRow, 1) = arrOut(outRow, 1) & OneCell.Value & vbCr

I tried arrOut(outRow, 1) = arrOut(outRow, 1) & oneCell.Value & Chr(10) and that worked for me. Thank you for you help!!!