Concatenate with a Line Break BUT....

[calinini]

Hello!

I have a exit interview survey that individuals are able to select multiple reasons as to why they are leaving the company. When I pull the raw data it puts all the reasons they selected into a new column. I would like to put all the reasons into one cell, which I can do using the CHAR(10) function, however if the person did not select a reason, it will still put a line break there.

Example Data:

[table="width: 500, class: grid, align: left"]
[tr]
[td]A[/td]
[td]B[/td]
[td]C[/td]
[td]D[/td]
[td]E[/td]
[td]F[/td]
[td]G[/td]
[td]H[/td]
[/tr]
[tr]
[td]CHOICES (Column)/EE Name (Row)[/td]
[td]Better pay[/td]
[td]Promotional position with more pay and/or responsibility[/td]
[td]Better benefits package to include tuition reimbursement, medical, 401k and vacation[/td]
[td]Better schedules[/td]
[td]Less responsibility for equal pay or more pay[/td]
[td]Other (please specify)[/td]
[td]<B>RESULTS</B>[/td]
[/tr]
[tr]
[td]EE A[/td]
[td][/td]
[td][/td]
[td][/td]
[td]Better schedules[/td]
[td][/td]
[td]Other (please specify)[/td]
[td]


Better schedules

Other (please specify)[/td]
[/tr]
[tr]
[td]EE B[/td]
[td]Better pay[/td]
[td]Promotional position with more pay and/or responsibility[/td]
[td][/td]
[td][/td]
[td]Less responsibility for equal pay or more pay[/td]
[td][/td]
[td]
Better pay
Promotional position with more pay and/or responsibility


Less responsibility for equal pay or more pay

[/td]
[/tr]

[/tr]
[tr]
[td]EE C[/td]
[td]Better pay[/td]
[td]Promotional position with more pay and/or responsibility[/td]
[td]Better benefits package to include tuition reimbursement, medical, 401k and vacation[/td]
[td][/td]
[td][/td]
[td][/td]
[td]
Better pay
Promotional position with more pay and/or responsibility
Better benefits package to include tuition reimbursement, medical, 401k and vacation


[/td]
[/tr]
[tr]


[/tr]
[/table]

My Formula in Col H is:
=CONCATENATE(a1,CHAR(10)&b1,CHAR(10)&c1,CHAR(10)&d1,CHAR(10)&e1,CHAR(10)&f1,CHAR(10)&g1)

As you can see it puts line breaks regardless to if that cell has value.

I would like the results of EE A to be:

Better schedules
Other (please specify)

So if that person didn't select that reason, it will not give it a line break. I have a feeling this is a macro or a VERY long If statement, which I'm not opposed to but I have to manually do this report monthly and I'm tired of copying and pasting. LOL

OR, if someone can tell me how to do a REPLACE function for a line break, that would work too!

Your help is greatly appreciated!

TIA!

 

[Takae]

hope this helps.

Sub sample1()
    Dim i As Long, str As String
    Dim rng As Range, C
    
    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        Set rng = Range(Cells(i, 2), Cells(i, 7))
        For Each C In rng
            If C <> "" Then
                str = str & vbCrLf & C
            End If
         Next
            Cells(i, 8).Value = Right(str, Len(str) - 2)
        str = ""
    Next
    
End Sub

 

[calinini]

Thank you Takae for your response.

I think I might be in a little over my head though. I've created your Macro in excel. I'm unsure of what I need to update in it though to make it work.

In my example, in column H, I want to put all the information in columns B:G for each row respectively.

How do I apply your macro so it will do that? It doesn't ask me which columns I would like to concatenate or anything of that sort.

Your help is appreciated.

 

[calinini]

Hi Takae, I just realized that your code does exactly what I need. THANK YOU!

As you can imagine my spreadsheet contains more information than my example. In reality, I'm trying to concatenate columns l:V into Column W. If you could show your code with those changes, I can do a comparison and see what you updated to indicate which columns to pull.

Thank you again!! I appreciate your help.

 

[Takae]

Column A is column H in reality?

Sub sample1()
Dim i As Long, str As String
Dim rng As Range, C

For i = 2 To Cells(Rows.Count, 8).End(xlUp).Row
Set rng = Range(Cells(i, 9), Cells(i, 22))
For Each C In rng
If C <> "" Then
str = str & vbCrLf & C
End If
Next
Cells(i, 23).Value = Right(str, Len(str) - 2)
str = ""
End Sub