How Can I merge these cells vertically?

[oball23]

SO I have data on an excel spreadsheet that looks like this below, with one questions vertically after another. How do I merge the data per question separately? Please let me know! Thanks guys!

#1 Whichofthe following is the paramount objective of
financial
reporting
by
state
and
local
governments?
a.
Reliability.
b.
Consistency.
c.
Comparability.
d.
Accountability.


It would look merged or something simple like this:

Whichofthe following is the paramount objective of financial reporting by state and local governments?
a. Reliability. b.Consistency.c. Comparability.d.Accountability.

 

[Webbarr]

Hey,

Try something like this -

It is worth noting that I am assuming the list of text you wish to concatenate is in column A. This will only work while the questions are in the active sheet. If you wish to be able to control this from a different sheet you would need to specify which sheet to use it in.

Sub mergeQs()

Dim i As String, k As String
Dim j As Byte


j = 0
k = ""


Range("B1").Activate
Do Until j = 5
    i = ActiveCell.Offset(0, -1).Value
    If Left(i, 1) = "#" And k <> "" Then
        ActiveCell.Offset(-1, 0).Value = Trim(k)
        k = ""
        j = 0
    End If
    If Left(i, 1) = "#" Then
        k = k & " " & ActiveCell.Offset(0, -1).Value
    End If
    If Left(i, 1) <> "#" And k <> "" Then
        k = k & " " & ActiveCell.Offset(0, -1).Value
    End If
    If j = 4 Then
        ActiveCell.Offset(-6, 0).Value = Trim(k)
    End If
    If ActiveCell.Offset(0, -1).Value = "" Then
        j = j + 1
    End If
    
    ActiveCell.Offset(1, 0).Activate
Loop






End Sub

 

[oball23]

Wow you are a genius!!

hey,

try something like this -

it is worth noting that i am assuming the list of text you wish to concatenate is in column a. This will only work while the questions are in the active sheet. If you wish to be able to control this from a different sheet you would need to specify which sheet to use it in.

sub mergeqs()

dim i as string, k as string
dim j as byte


j = 0
k = ""


range("b1").activate
do until j = 5
    i = activecell.offset(0, -1).value
    if left(i, 1) = "#" and k <> "" then
        activecell.offset(-1, 0).value = trim(k)
        k = ""
        j = 0
    end if
    if left(i, 1) = "#" then
        k = k & " " & activecell.offset(0, -1).value
    end if
    if left(i, 1) <> "#" and k <> "" then
        k = k & " " & activecell.offset(0, -1).value
    end if
    if j = 4 then
        activecell.offset(-6, 0).value = trim(k)
    end if
    if activecell.offset(0, -1).value = "" then
        j = j + 1
    end if
    
    activecell.offset(1, 0).activate
loop






end sub

 

[Peter_SSs]

@oball23
Welcome to the MrExcel board!

I understand that you have a solution that you are happy with, but I'm just wondering if your various questions down the column have a blank row between?

Also is every question exactly 16 rows like the one sample you gave?

 

[oball23]

Thanks for your response.

No breaks and each set of questions has a different number of rows- so some may have 16 some may 10, 9 etc.

I hope this makes sense? It is pretty complicated and not sure if it can be done in Excel, perhaps MS Word?

@oball23
Welcome to the MrExcel board!

I understand that you have a solution that you are happy with, but I'm just wondering if your various questions down the column have a blank row between?

Also is every question exactly 16 rows like the one sample you gave?

 

[Peter_SSs]

Wow you are a genius!!

.. not sure if it can be done in Excel..

These two statements seem quite contradictory to say the least!
Did Webbarr's code do what you wanted or not?

 

[oball23]

These two statements seem quite contradictory to say the least!
Did Webbarr's code do what you wanted or not?

The code did NOT do what I wanted however it was still genius

 

[Peter_SSs]

The code did NOT do what I wanted however it was still genius

In that case your response in post #3 was extremely odd as I, and I'm sure Webbarr and any other readers interpreted that as indicating the code was exactly what you wanted. I only posted at all because I had in my mind a possibility of doing much the same as Webarr's code but in a more compact & efficient way.

Without any criticism of Webarr's code, I don't see how it's genius if it doesn't do what you asked for.

So, In what way did it fail?

Could you give an example with, say, 3 sets of question data and show us what the expected results are and where those results should go?

 

[oball23]

In that case your response in post #3 was extremely odd as I, and I'm sure Webbarr and any other readers interpreted that as indicating the code was exactly what you wanted. I only posted at all because I had in my mind a possibility of doing much the same as Webarr's code but in a more compact & efficient way.

Without any criticism of Webarr's code, I don't see how it's genius if it doesn't do what you asked for.

So, In what way did it fail?

Could you give an example with, say, 3 sets of question data and show us what the expected results are and where those results should go?

No offense to anyone and I really appreciate the effort. However I simply went copied and pasted the data into Word and did a simple find and replace and that worked out well....Thanks again.